<html>
<!--
  == This software is subject to the terms of the Eclipse Public License v1.0
  == Agreement, available at the following URL:
  == http://www.eclipse.org/legal/epl-v10.html.
  == You must accept the terms of that agreement to use this software.
  ==
  == Copyright (C) 2001-2005 Julian Hyde
  == Copyright (C) 2005-2012 Pentaho
  == All Rights Reserved.
  ==
  == jhyde, 24 September, 2002
  -->
<head>
    <link rel="stylesheet" type="text/css" href="stylesheet.css"/>
	<title>Pentaho Analysis Services: How to Design a Mondrian Schema</title>
</head>
<body>
<!-- doc2web start -->

<!-- page title -->
<div class="contentheading">How to Design a Mondrian Schema</div>
<!-- end page title -->


<!--
########################
##  Contents       #####
######################## -->
<ol>
  	<li><a href="#What_is_a_schema">What is a schema?</a></li>
  	<li><a href="#Schema_files">Schema files</a>
		<ol>
			<li><a href="#Annotation">Annotation</a></li>
	    </ol>
	</li>
  	<li><a href="#Cubes_and_dimensions">Logical model</a>
		<ol>
			<li><a href="#Cube">Cube</a></li>
			<li><a href="#Measures">Measures</a></li>
			<li><a href="#Dimensions,_Hierarchies,_Levels">Dimensions, Hierarchies, Levels</a></li>
				<ol>
					<li><a href="#Mapping_Hierarchies">Mapping dimensions and hierarchies onto tables</a></li>
					<li><a href="#The_all_member">The 'All' member</a></li>
					<li><a href="#Time_dimensions">Time dimensions</a></li>
					<li><a href="#Level_Order_and_Display">Order and Display of Levels</a></li>
					<li><a href="#Multiple_hierarchies">Multiple hierarchies</a></li>
					<li><a href="#Degenerate_dimensions">Degenerate dimensions</a></li>
					<li><a href="#Inline_tables">Inline tables</a></li>
					<li><a href="#Member_properties_and_formatters">Member properties and formatters</a></li>
                    <li><a href="#Approximate_level_cardinality">Approximate level cardinality</a></li>
					<li><a href="#Default_Measure_Attribute">Default Measure Attribute</a></li>
                    <li><a href="#Functional_dependency_optimizations">Functional Dependency Optimizations</a></li>
                    <li><a href="#Table_hints">Table Hints</a></li>
                </ol>
		</ol>
  	</li>
  	<li><a href="#Star_schemas">Star and snowflake schemas</a>
		<ol>
			<li><a href="#Shared_dimensions">Shared dimensions</a></li>
			<li><a href="#Join_optimization">Join optimization</a></li>
		</ol>
  	</li>
  	<li><a href="#Advanced_logical_constructs">Advanced logical constructs</a>
		<ol>
			<li><a href="#Virtual_cubes">Virtual cubes</a></li>
			<li><a href="#Parent_child_hierarchies">Parent-child hierarchies</a><ol>
			<li><a href="#Tuning_parent-child_hierarchies">Tuning parent-child 
			hierarchies</a></li>
			<li><a href="#Closure_tables">Closure tables</a></li>
			<li><a href="#Populating_closure_tables">Populating closure tables</a></li>
		</ol>
			</li>
			<li><a href="#Member_properties">Member properties</a></li>
			<li><a href="#Calculated_members">Calculated members</a></li>
			<li><a href="#Named_sets">Named sets</a></li>
		</ol>
	</li>
  	<li><a href="#Plugins">Plug-ins</a>
		<ol>
			<li><a href="#User-defined_function">User-defined function</a></li>
			<li><a href="#Member_reader">Member reader</a></li>
			<li><a href="#Cell_reader">Cell reader</a></li>
			<li><a href="#Cell_formatter">Cell formatter</a></li>
			<li><a href="#Member_formatter">Member formatter</a></li>
			<li><a href="#Property_formatter">Property formatter</a></li>
			<li><a href="#Schema_processor">Schema processor</a></li>
			<li><a href="#DataSource_change_listener">Data source change listener</a></li>
            <li><a href="#Dynamic_datasource_xmla_servlet">Dynamic datasource xmla servlet</a></li>
		</ol>
	</li>
  	<li><a href="#I18n">Internationalization</a></li>
  	<li><a href="#Aggregate_tables">Aggregate tables</a></li>
  	<li><a href="#Access_control">Access-control</a>
		<ol>
			<li><a href="#Defining_roles">Defining a role</a></li>
			<li><a href="#Rollup_policy">Rollup policy</a></li>
			<li><a href="#Union_roles">Union roles</a></li>
			<li><a href="#Setting_a_connections_role">Setting a connection's role</a></li>
		</ol>
	</li>
  	<li><a href="#Appendix_A_XML_elements">Appendix A: XML elements</a></li>
</ol>

<!--
#############################
##  1. What is a Schema #####
############################# -->

<h3>1. What is a schema?<a name="What_is_a_schema">&nbsp;</a></h3>
<p>A schema defines a multi-dimensional database. It contains a logical model, consisting of cubes, hierarchies, and members, and a mapping of this model onto a physical model.</p>
<p>The logical model consists of the constructs used to write queries in MDX language: cubes, dimensions, hierarchies, levels, and members.</p>
<p>The physical model is the source of the data which is presented through the logical model. It is typically a star schema, which is a set of tables in a relational database; later, we shall see examples of other kinds of mappings.</p>
<!--
###########################
##  2. overview start #####
########################### -->
<h3>2. Schema files<a name="Schema_files">&nbsp;</a></h3>
<p>Mondrian schemas are represented in an XML file. An example schema, containing almost all of the
constructs we discuss here, is supplied as <code>demo/FoodMart.xml</code> in the Mondrian distribution.
The dataset to populate this schema is <a href="install.html#2_Set_up_test_data">also in the distribution</a>.</p>
<p>Currently, the only way to create a schema is to edit a schema XML file in a text editor. The XML
syntax is not too complicated, so this is not as difficult as it sounds, particularly if you use the FoodMart
schema as a guiding example.</p>

<p>The structure of the XML document is as follows:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px">&lt;<a href="#XML_Schema">Schema</a>&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Cube">Cube</a>&gt;</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_Table">Table</a>&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_AggName">AggName</a>&gt;</div>
                        <div style="padding-left:100px;">aggElements</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_AggPattern">AggPattern</a>&gt;</div>
                        <div style="padding-left:100px;">aggElements</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_Dimension">Dimension</a>&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_Hierarchy">Hierarchy</a>&gt;</div>
                        <div style="padding-left:100px;">relation</div>
                        <div style="padding-left:100px;">&lt;<a href="#XML_Closure">Closure</a>/&gt;</div>
                        <div style="padding-left:100px;">&lt;<a href="#XML_Level">Level</a>&gt;</div>
                            <div style="padding-left:120px;">&lt;<a href="#XML_KeyExpression">KeyExpression</a>&gt;</div>
                                <div style="padding-left:140px;">&lt;<a href="#XML_SQL">SQL</a>/&gt;</div>
                            <div style="padding-left:120px;">&lt;<a href="#XML_NameExpression">NameExpression</a>&gt;</div>
                                <div style="padding-left:140px;">&lt;<a href="#XML_SQL">SQL</a>/&gt;</div>
                            <div style="padding-left:120px;">&lt;<a href="#XML_CaptionExpression">CaptionExpression</a>&gt;</div>
                                <div style="padding-left:140px;">&lt;<a href="#XML_SQL">SQL</a>/&gt;</div>
                            <div style="padding-left:120px;">&lt;<a href="#XML_OrdinalExpression">OrdinalExpression</a>&gt;</div>
                                <div style="padding-left:140px;">&lt;<a href="#XML_SQL">SQL</a>/&gt;</div>
                            <div style="padding-left:120px;">&lt;<a href="#XML_ParentExpression">ParentExpression</a>&gt;</div>
                                <div style="padding-left:140px;">&lt;<a href="#XML_SQL">SQL</a>/&gt;</div>
                            <div style="padding-left:120px;">&lt;<a  href="#XML_Property">Property</a>&gt;</div>
                                <div style="padding-left:140px;">&lt;<a href="#XML_PropertyExpression">PropertyExpression</a>&gt;</div>
                                    <div style="padding-left:160px;">&lt;<a href="#XML_SQL">SQL</a>/&gt;</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_DimensionUsage">DimensionUsage</a>&gt;</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_Measure">Measure</a>&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_MeasureExpression">MeasureExpression</a>&gt;</div>
                        <div style="padding-left:100px;">&lt;<a href="#XML_SQL">SQL</a>/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_MemberProperty">CalculatedMemberProperty</a>/&gt;</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_CalculatedMember">CalculatedMember</a>&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_Formula">Formula</a>/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_MemberProperty">CalculatedMemberProperty</a>/&gt;</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_NamedSet">NamedSet</a>&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_Formula">Formula</a>/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_VirtualCube">VirtualCube</a>&gt;</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_CubeUsages">CubeUsages</a>&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_CubeUsage">CubeUsage</a>&gt;</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_VirtualCubeDimension">VirtualCubeDimension</a>&gt;</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_VirtualCubeMeasure">VirtualCubeMeasure</a>&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Role">Role</a>&gt;</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_SchemaGrant">SchemaGrant</a>&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_CubeGrant">CubeGrant</a>&gt;</div>
                        <div style="padding-left:100px;">&lt;<a href="#XML_DimensionGrant">DimensionGrant</a>&gt;</div>
                        <div style="padding-left:100px;">&lt;<a href="#XML_HierarchyGrant">HierarchyGrant</a>&gt;</div>
                            <div style="padding-left:120px;">&lt;<a href="#XML_MemberGrant">MemberGrant</a>/&gt;</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_Union">Union</a>&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_RoleUsage">RoleUsage</a>/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_UserDefinedFunction">UserDefinedFunction</a>/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Parameter">Parameter</a>/&gt;</div>
        <br/>
        relation ::=<br/>
            <div style="padding-left:40px;">&lt;<a href="#XML_Table">Table</a>&gt;</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_SQL">SQL</a>/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_View">View</a>&gt;</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_SQL">SQL</a>/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_InlineTable">InlineTable</a>&gt;</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_ColumnDefs">ColumnDefs</a>&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_ColumnDef">ColumnDef</a>&gt;</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_Rows">Rows</a>&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_Row">Row</a>&gt;</div>
                        <div style="padding-left:100px;">&lt;<a href="#XML_Value">Value</a>&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Join">Join</a>&gt;</div>
                <div style="padding-left:60px;">relation</div>
        <br/>
        aggElement ::=<br/>
            <div style="padding-left:40px;">&lt;<a href="#XML_AggExclude">AggExclude</a>&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_AggFactCount">AggFactCount</a>&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_AggMeasureFactCount">AggMeasureFactCount</a>&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_AggIgnoreColumn">AggIgnoreColumn</a>&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_AggForeignKey">AggForeignKey</a>&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_AggMeasure">AggMeasure</a>&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_AggLevel">AggLevel</a>&gt;</div>
    </code>
</blockquote>

<p><b>NOTE</b>: The order of XML elements is important. For example, the
<code><a href="#XML_UserDefinedFunction">&lt;UserDefinedFunction&gt;</a></code>
element has to occur inside the <code><a href="#XML_Schema">&lt;Schema&gt;</a></code>
element after all collections of <code><a href="#XML_Cube">&lt;Cube&gt;</a></code>,
<code><a href="#XML_VirtualCube">&lt;VirtualCube&gt;</a></code>,
<code><a href="#XML_NamedSet">&lt;NamedSet&gt;</a></code>
and <code><a href="#XML_Role">&lt;Role&gt;</a></code> elements. If you include it before the first
<code><a href="#XML_Cube">&lt;Cube&gt;</a></code> element,
the rest of the schema will be ignored.</p>

<p>The content of each XML element is described in
<a href="#Appendix_A_XML_elements">Appendix A</a> and in the <a href="xml_schema.html">XML schema</a>.</p>

<!--
##########################
##  2.1 Annotations  #####
########################## -->
<h1>2.1 Annotation<a name="Annotation">&nbsp;</a></h1>
<p>The major element types (schema, cube, virtual cube, shared dimension, 
dimension, hierarchy, level, measure, calculated member) support annotations. An 
annotation is a way of associating a user-defined property with a metadata 
element, and in particular, allows tools to add metadata without extending the 
official Mondrian schema.</p>

<p>Create an 
<code><a href="#XML_Annotations">&lt;Annotations&gt;</a></code> element as a child of the element you wish to
annotate (generally it is the first child element, but check the schema
definition for details), then include a number of 
<code><a href="#XML_Annotation">&lt;Annotation&gt;</a></code> elements. 
<code><a href="#XML_Annotation">&lt;Annotation&gt;</a></code>
elements' names must be unique within their element. If you are adding 
annotations to support a particular tool that you maintain, choose annotation 
names carefully, to ensure that they do not clash with annotations used by other 
tools.</p>

<p>The following example shows &quot;Author&quot; and &quot;Date&quot; annotations attached to a <code><a href="#XML_Schema">&lt;Schema&gt;</a></code> object.</p>

<blockquote style="text-indent: -20px">
	<code>
  		<div style="padding-left:20px;">&lt;<a href="#XML_Schema">Schema</a> name=&quot;Rock Sales&quot;&gt;</div>
			<div style="padding-left:40px;;">&lt;<a href="#XML_Annotations">Annotations</a>&gt;</div>
			<div style="padding-left:60px;;">&lt;<a href="#XML_Annotation">Annotation</a> name="Author"&gt;Fred Flintstone&lt;/<a href="#XML_Annotation">Annotation</a>&gt;</div>
			<div style="padding-left:60px;;">&lt;<a href="#XML_Annotation">Annotation</a> name="Date"&gt;10,000 BC&lt;/<a href="#XML_Annotation">Annotation</a>&gt;</div>
			<div style="padding-left:40px;;">&lt;/<a href="#XML_Annotations">Annotations</a>&gt;</div>
			<div style="padding-left:40px;;">&lt;<a href="#XML_Cube">Cube</a> name="Sales"&gt;</div>
			<div style="padding-left:60px;;">...</div>
</code>
</blockquote>
<!--
###########################
##  3. Logical model  #####
########################### -->
<h3>3. Logical model<a name="Cubes_and_dimensions">&nbsp;</a></h3>
<p>The most important components of a schema are cubes, measures, and dimensions:</p>
<ul>
  	<li>A <dfn>cube</dfn> is a collection of dimensions and measures in a particular subject area. </li>
  	<li>A <dfn>measure</dfn> is a quantity that you are interested in measuring, for example, unit sales of a
	product, or cost price of inventory items.</li>
  	<li>A <dfn>dimension</dfn> is an attribute, or set of attributes, by which you can divide measures into
	sub-categories. For example, you might wish to break down product sales by their color, the gender of the
	customer, and the store in which the product was sold; color, gender, and store are all dimensions.</li>
</ul>
<p>Let's look at the XML definition of a simple schema.</p>

<blockquote style="text-indent: -20px">
	<code>
  		<div style="padding-left:20px;">&lt;<a href="#XML_Schema">Schema</a>&gt;</div>
			<div style="padding-left:40px;;">&lt;<a href="#XML_Cube">Cube</a> name="Sales"&gt;</div>
				<div style="padding-left:60px;">&lt;<a href="#XML_Table">Table</a> name="sales_fact_1997"/&gt;</div>
				<div style="padding-left:60px;">&lt;<a href="#XML_Dimension">Dimension</a> name="Gender" foreignKey="customer_id"&gt;</div>
					<div style="padding-left:80px;">&lt;<a href="#XML_Hierarchy">Hierarchy</a> hasAll="true" allMemberName="All Genders" primaryKey="customer_id"&gt;</div>
						<div style="padding-left:100px;">&lt;<a href="#XML_Table">Table</a> name="customer"/&gt;</div>
						<div style="padding-left:100px;">&lt;<a href="#XML_Level">Level</a> name="Gender" column="gender" uniqueMembers="true"/&gt;</div>
					<div style="padding-left:80px;">&lt;/<a href="#XML_Hierarchy">Hierarchy</a>&gt;</div>
				<div style="padding-left:60px;">&lt;/<a href="#XML_Dimension">Dimension</a>&gt;</div>
				<div style="padding-left:60px;">&lt;<a href="#XML_Dimension">Dimension</a> name="Time" foreignKey="time_id"&gt;</div>
					<div style="padding-left:80px;">&lt;<a href="#XML_Hierarchy">Hierarchy</a> hasAll="false" primaryKey="time_id"&gt;</div>
						<div style="padding-left:100px;">&lt;<a href="#XML_Table">Table</a> name="time_by_day"/&gt;</div>
						<div style="padding-left:100px;">&lt;<a href="#XML_Level">Level</a> name="Year" column="the_year" type="Numeric" uniqueMembers="true"/&gt;</div>
						<div style="padding-left:100px;">&lt;<a href="#XML_Level">Level</a> name="Quarter" column="quarter" uniqueMembers="false"/&gt;</div>
						<div style="padding-left:100px;">&lt;<a href="#XML_Level">Level</a> name="Month" column="month_of_year" type="Numeric" uniqueMembers="false"/&gt;</div>
					<div style="padding-left:80px;">&lt;/<a href="#XML_Hierarchy">Hierarchy</a>&gt;</div>
				<div style="padding-left:60px;">&lt;/<a href="#XML_Dimension">Dimension</a>&gt;</div>
				<div style="padding-left:60px;">&lt;<a href="#XML_Measure">Measure</a> name="Unit Sales" column="unit_sales" aggregator="sum" formatString="#,###"/&gt;</div>
				<div style="padding-left:60px;">&lt;<a href="#XML_Measure">Measure</a> name="Store Sales" column="store_sales" aggregator="sum" formatString="#,###.##"/&gt;</div>
				<div style="padding-left:60px;">&lt;<a href="#XML_Measure">Measure</a> name="Store Cost" column="store_cost" aggregator="sum" formatString="#,###.00"/&gt;</div>
				<div style="padding-left:60px;">&lt;<a href="#XML_CalculatedMember">CalculatedMember</a> name="Profit" dimension="Measures" formula="[Measures].[Store Sales] - [Measures].[Store Cost]"&gt;</div>
					<div style="padding-left:80px;">&lt;<a href="#XML_CalculatedMemberProperty">CalculatedMemberProperty</a> name="FORMAT_STRING" value="$#,##0.00"/&gt;</div>
				<div style="padding-left:60px;">&lt;/<a href="#XML_CalculatedMember">CalculatedMember</a>&gt;</div>
			<div style="padding-left:40px;">&lt;/<a href="#XML_Cube">Cube</a>&gt;</div>
		<div style="padding-left:20px">&lt;/<a href="#XML_Schema">Schema</a>&gt;</div>
	</code>
</blockquote>

<p>This schema contains a single cube, called "Sales". The Sales cube has two dimensions,
"Time", and "Gender", and four measures, "Unit Sales", "Store Sales", "Store Cost", and "Profit".</p>

<p>We can write an MDX query on this schema:</p>

<blockquote style="text-indent: -20px">
  	<code>
		SELECT {[Measures].[Unit Sales], [Measures].[Store Sales]} ON COLUMNS,<br/>
		&nbsp; {descendants([Time].[1997].[Q1])} ON ROWS<br/>
		FROM [Sales]<br/>
		WHERE [Gender].[F]
	</code>
</blockquote>

<p>This query refers to the Sales cube (<code>[Sales]</code>), each of the dimensions
<code>[Measures]</code>, <code>[Time]</code>, <code>[Gender</code>], and various members
of those dimensions. The results are as follows:</p>

<blockquote>
  	<table class="grayTable" id="AutoNumber1"  >
      	<tr>
			<td align="left"><code>[Time]</code></td>
			<td><code>[Measures].[Unit Sales]</code></td>
			<td><code>[Measures].[Store Sales]</code></td>
		</tr>
		<tr>
			<td align="left"><code>[1997].[Q1]</code></td>
			<td>0</td>
        	<td>0</td>
      	</tr>
      	<tr>
        	<td align="left"><code>[1997].[Q1].[Jan]</code></td>
        	<td>0</td>
        	<td>0</td>
      	</tr>
      	<tr>
        	<td align="left"><code>[1997].[Q1].[Feb]</code></td>
        	<td>0</td>
        	<td>0</td>
      	</tr>
      	<tr>
        	<td align="left"><code>[1997].[Q1].[Mar]</code></td>
        	<td>0</td>
        	<td>0</td>
  		</tr>
	</table>
</blockquote>
<p>Now let's look at the schema definition in more detail.</p>

<!--
########################
##  3.1 Cube       #####
######################## -->
<h1>3.1 Cube<a name="Cube">&nbsp;</a></h1>

<p>A cube (see <code><a href="#XML_Cube">&lt;Cube&gt;</a></code>) is a named collection of measures
and dimensions. The one thing the measures and dimensions have in common is the fact table, here
<code>"sales_fact_1997"</code>. As we shall see, the fact table holds the columns
from which measures are calculated, and contains references to the tables which hold the dimensions.</p>

<blockquote style="text-indent: -20px">
  	<code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Cube">Cube</a> name="Sales"&gt;</div>
			<div style="padding-left:40px;">&lt;<a href="#XML_Table">Table</a> name="sales_fact_1997"/&gt;</div>
			<div style="padding-left:40px;">...</div>
		<div style="padding-left:20px">&lt;/<a href="#XML_Cube">Cube</a>&gt;</div>
	</code>
</blockquote>

<p>The fact table is defined using the <code><a href="#XML_Table">&lt;Table&gt;</a></code> element. If the fact table is not in
the default schema, you can provide an explicit schema using the "schema" attribute, for example</p>

<blockquote>
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Table">Table</a> schema=" dmart" name="sales_fact_1997"/&gt;</div>
    </code>
</blockquote>

<p>You can also use the <code><a href="#XML_View">&lt;View&gt;</a></code>
construct to build more complicated SQL statements. The
<code><a href="#XML_Join">&lt;Join&gt;</a></code> construct is not supported for fact tables.</p>

<!--
########################
##  3.2 Measures   #####
######################## -->
<h1>3.2 Measures<a name="Measures">&nbsp;</a></h1>
<p>The Sales cube defines several measures, including "Unit Sales" and "Store Sales".</p>

<blockquote style="text-indent: -20px">
  	<code>
		<div style="padding-left:20px;">&lt;<a href="#XML_Measure">Measure</a> name="Unit Sales" column="unit_sales" aggregator="sum" datatype="Integer" formatString="#,###"/&gt;</div>
		<div style="padding-left:20px;">&lt;<a href="#XML_Measure">Measure</a> name="Store Sales" column="store_sales" aggregator="sum" datatype="Numeric" formatString="#,###.00"/&gt;</div>
	</code>
</blockquote>

<p>Each measure (see <code><a href="#XML_Measure">&lt;Measure&gt;</a></code>) has a name, a column in the fact table, and an
<code>aggregator</code>. The aggregator is usually "sum", but "count", "min", "max", "avg", and
"distinct-count" are also allowed; "distinct-count" has some limitations if your cube contains a
<a href="#Parent_child_hierarchies">parent-child hierarchy</a>.</p>

<p>The optional <code>datatype</code> attribute specifies how cell values are represented in Mondrian's
cache, and how they are returned via XML for Analysis. The <code>datatype</code> attribute can have
values "<code>String</code>", "<code>Integer</code>", "<code>Numeric</code>", &quot;<code>Boolean</code>&quot;,
&quot;<code>Date</code>&quot;, &quot;<code>Time</code>&quot;, and &quot;<code>Timestamp</code>&quot;.
The default is "<code>Numeric</code>", except for "<code>count</code>" and
"<code>distinct-count</code>" measures, which are "<code>Integer</code>".</p>

<p>An optional <code>formatString</code> attribute specifies how the value is to be printed.
Here, we have chosen to output unit sales with no decimal places (since it is an integer), and store sales
with two decimal places (since it is a currency value). The ',' and '.' symbols are locale-sensitive, so if
you were running in Italian, store sales might appear as "48.123,45". You can achieve even more wild effects
using <a href="mdx.html#Format_strings">advanced format strings</a>.</p>

<p>A measure can have a caption attribute to be returned by the
<code><a href="http://sourceforge.net/api/mondrian/olap/Member.html#getCaption%28%29">Member.getCaption()</a></code>
method instead of the name. Defining a specific caption does make sense if special letters (e.g. &#931; or &#928;)
are to be displayed:</p>

<blockquote style="text-indent: -20px">
  	<code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Measure">Measure</a> name="Sum X" column="sum_x" aggregator="sum" caption="&amp;#931; X"/&gt;</div>
    </code>
</blockquote>

<p>Rather than coming from a column, a measure can use a <a href="#Cell_reader">cell reader</a>,
or a measure can use a SQL expression to calculate its value. The measure "Promotion Sales" is
an example of this.</p>

<blockquote style="text-indent: -20px">
  	<code>
		<div style="padding-left:20px">&lt;<a href="#XML_Measure">Measure</a> name="Promotion Sales" aggregator="sum" formatString="#,###.00"&gt;</div>
			<div style="padding-left:40px;">&lt;<a href="#XML_MeasureExpression">MeasureExpression</a>&gt;</div>
				<div style="padding-left:60px;">&lt;<a href="#XML_SQL">SQL</a> dialect="generic"&gt;</div>
					<div style="padding-left:80px;">(case when sales_fact_1997.promotion_id = </div>
					<div style="padding-left:80px;">0 then 0 else sales_fact_1997.store_sales end)</div>
				<div style="padding-left:60px;">&lt;/<a href="#XML_SQL">SQL</a>&gt;</div>
			<div style="padding-left:40px;">&lt;/<a href="#XML_MeasureExpression">MeasureExpression</a>&gt;</div>
        <div style="padding-left:20px">&lt;/<a href="#XML_Measure">Measure</a>&gt;</div>
 	</code>
</blockquote>

<p>In this case, sales are only included in the summation if they correspond to a promotion sale.
Arbitrary SQL expressions can be used, including subqueries. However, the underlying database
must be able to support that SQL expression in the context of an aggregate. Variations in syntax
between different databases is handled by specifying the dialect in the SQL tag.</p>

<p>In order to provide a specific formatting of the cell values, a measure can use a
<a href="#Cell_formatter">cell formatter</a>.</p>
<!--
#############################################
##  3.3 Dimensions, Hierarchies, Levels #####
############################################# -->
<h1>3.3 Dimensions, Hierarchies, Levels<a name="Dimensions,_Hierarchies,_Levels">&nbsp;</a></h1>
<p>Some more definitions:</p>
<ul>
  	<li>A <dfn>member</dfn> is a point within a dimension determined by a particular set of attribute
	values. The gender hierarchy has the two members 'M' and 'F'. 'San Francisco', 'California' and
	'USA' are all members of the store hierarchy.</li>
  	<li>A <dfn>hierarchy</dfn> is a set of members organized into a structure for convenient analysis.
	For example, the store hierarchy consists of the store name, city, state, and nation. The
	hierarchy allows you to form intermediate sub-totals: the sub-total for a state is the sum of the
	sub-totals of all of the cities in that state, each of which is the sum of the sub-totals of
	the stores in that city.</li>
	<li>A <dfn>level</dfn> is a collection of members which have the
	same distance from the root of the hierarchy.</li>
  	<li>A <dfn>dimension</dfn> is a collection of hierarchies which discriminate on the same fact table attribute (say, the day that a sale occurred).</li>
</ul>
<p>For reasons of uniformity, measures are treated as members of a special dimension, called 'Measures'.</p>
<!--
########################
##  An example     #####
######################## -->
<h1>An example</h1>

<p>Let's look at a simple dimension. </p>

<blockquote style="text-indent: -20px">
  	<code>
        <div style="padding-left:20px">&lt;<a href="#XML_Dimension">Dimension</a> name="Gender" foreignKey="customer_id"&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Hierarchy">Hierarchy</a> hasAll="true" primaryKey="customer_id"&gt;</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_Table">Table</a> name="customer"/&gt;</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_Level">Level</a> name="Gender" column="gender" uniqueMembers="true"/&gt;</div>
            <div style="padding-left:40px;">&lt;/<a href="#XML_Hierarchy">Hierarchy</a>&gt;</div>
        <div style="padding-left:20px">&lt;/<a href="#XML_Dimension">Dimension</a>&gt;</div>
 	</code>
</blockquote>

<p>This dimension consists of a single hierarchy, which consists of a single level called <code>Gender</code>.
(As we shall see <a href="#The_all_member">later</a>, there is also a special level called
<code>[(All)]</code> containing a grand total.)</p>

<p>The values for the dimension come from the <code>gender</code> column in the <code>customer</code>
table. The "gender" column contains two values, 'F' and 'M', so the Gender dimension contains the members
<code>[Gender].[F]</code> and <code>[Gender].[M]</code>.</p>

<p>For any given sale, the gender dimension is the gender of the customer who made that purchase. This is expressed by
joining from the fact table "sales_fact_1997.customer_id" to the dimension table "customer.customer_id".</p>
<!--
##############################################################
##  3.3.1 Mapping dimensions and hierarchies onto tables #####
############################################################## -->
<h1>3.3.1 Mapping dimensions and hierarchies onto tables<a name="Mapping_Hierarchies">&nbsp;</a></h1>

<p>A dimension is joined to a cube by means of a pair of columns, one in the fact table, the other in the dimension table.
The <code>&lt;Dimension&gt;</code> element has a <code>foreignKey</code> attribute,
which is the name of a column in the fact table; the <code>&lt;Hierarchy&gt;</code> element has
a <code>primaryKey</code> attribute.</p>

<p>If the hierarchy has more than one table, you can disambiguate using the <code>primaryKeyTable</code>
attribute. </p>

<p>The <code>column</code> attribute defines the key of the level. It must be
the name of a column in the level's table. If the key is an expression, you can 
instead use the <code>&lt;KeyExpression&gt;</code> element inside the Level. The following is 
equivalent to the above example:</p>

<blockquote style="text-indent: -20px">
	<code>
        <div style="padding-left:20px">&lt;<a href="#XML_Dimension">Dimension</a> name="Gender" foreignKey="customer_id"&gt;</div>
            <div style="padding-left:40px">&lt;<a href="#XML_Hierarchy">Hierarchy</a> hasAll="true" primaryKey="customer_id"&gt;</div>
                <div style="padding-left:60px">&lt;<a href="#XML_Table">Table</a> name="customer"/&gt;</div>
                <div style="padding-left:60px">&lt;<a href="#XML_Level">Level</a> name="Gender" column="gender" uniqueMembers="true"&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_KeyExpression">KeyExpression</a>&gt;</div>
                        <div style="padding-left:100px;">&lt;<a href="#XML_SQL">SQL</a> dialect=&quot;generic&quot;&gt;customer.gender&lt;/<a href="#XML_SQL">SQL</a>&gt;</div>
                    <div style="padding-left:80px;">&lt;/<a href="#XML_KeyExpression">KeyExpression</a>&gt;</div>
                <div style="padding-left:60px;">&lt;/<a href="#XML_Level">Level</a>&gt;</div>
            <div style="padding-left:40px;">&lt;/<a href="#XML_Hierarchy">Hierarchy</a>&gt;</div>
        <div style="padding-left:20px">&lt;/<a href="#XML_Dimension">Dimension</a>&gt;</div>
    </code>
</blockquote>

<p>Other attributes of <code>&lt;Level&gt;</code>, <code>&lt;Measure&gt;</code>
and <code>&lt;Property&gt;</code> have corresponding nested elements:</p>

	<table class="grayTable" id="table11" width="500">
  		<tr>
    		<td><b>Parent element</b></td>
    		<td><b>Attribute</b></td>
    		<td><b>Equivalent nested element</b></td>
    		<td><b>Description</b></td>
  		</tr>
  		<tr>
    		<td><code>&lt;<a href="#XML_Level">Level</a>&gt;</code></td>
    		<td><code>column</code></td>
    		<td><code>&lt;<a href="#XML_KeyExpression">KeyExpression</a>&gt;</code></td>
    		<td>Key of level.</td>
  		</tr>
  		<tr>
    		<td><code>&lt;<a href="#XML_Level">Level</a>&gt;</code></td>
    		<td><code>nameColumn</code></td>
    		<td><code>&lt;<a href="#XML_NameExpression">NameExpression</a>&gt;</code></td>
    		<td>Expression which defines the name of members of this level. If 
			not specified, the level key is used.</td>
  		</tr>
  		<tr>
    		<td><code>&lt;<a href="#XML_Level">Level</a>&gt;</code></td>
    		<td><code>ordinalColumn</code></td>
    		<td><code>&lt;<a href="#XML_OrdinalExpression">OrdinalExpression</a>&gt;</code></td>
    		<td>Expression which defines the order of members. If not specified, 
			the level key is used.</td>
  		</tr>
  		<tr>
    		<td><code>&lt;<a href="#XML_Level">Level</a>&gt;</code></td>
    		<td><code>captionColumn</code></td>
    		<td><code>&lt;<a href="#XML_CaptionExpression">CaptionExpression</a>&gt;</code></td>
    		<td>Expression which forms the caption of members. If not specified, 
			the level name is used.</td>
  		</tr>
  		<tr>
    		<td><code>&lt;<a href="#XML_Level">Level</a>&gt;</code></td>
    		<td><code>parentColumn</code></td>
    		<td><code>&lt;<a href="#XML_ParentExpression">ParentExpression</a>&gt;</code></td>
    		<td>Expression by which child members reference their parent member 
			in a parent-child hierarchy. Not specified in a regular hierarchy.</td>
  		</tr>
  		<tr>
    		<td><code>&lt;<a href="#XML_Measure">Measure</a>&gt;</code></td>
    		<td><code>column</code></td>
    		<td><code>&lt;<a href="#XML_MeasureExpression">MeasureExpression</a>&gt;</code></td>
    		<td>SQL expression to calculate the value of the measure (the 
			argument to the SQL aggregate function).</td>
  		</tr>
  		<tr>
    		<td><code>&lt;<a href="#XML_Property">Property</a>&gt;</code></td>
    		<td><code>column</code></td>
    		<td><code>&lt;<a href="#XML_PropertyExpression">PropertyExpression</a>&gt;</code></td>
    		<td>SQL expression to calculate the value of the property.</td>
  		</tr>
  		</table>

<p>The <code>uniqueMembers</code> attribute is used to optimize SQL generation. If you know that the
values of a given level column in the dimension table are unique across all the other values in that column across
the parent levels, then set <code>uniqueMembers="true"</code>, otherwise, set to
<code>"false"</code>. For example, a time dimension like <code>[Year].[Month]</code>
will have <code>uniqueMembers="false"</code> at the Month level, as the same month appears in different
years. On the other hand, if you had a <code>[Product Class].[Product Name]</code> hierarchy, and you
were sure that <code>[Product Name]</code> was unique, then you can set
<code>uniqueMembers="true"</code>. If you are not sure, then always set
<code>uniqueMembers="false"</code>. At the top level, this will always be
<code>uniqueMembers="true"</code>, as there is no parent level.</p>

<p>The <code>highCardinality</code> attribute is used to notify Mondrian
there are undefined and very high number of elements for this dimension.
Acceptable values are <code>true</code> or <code>false</code> (last one
is default value). Actions performed over the whole set of dimension elements
cannot be performed when using <code>highCardinality="true"</code>.
</p>

<!--
################################
##  3.3.2 The 'all' member #####
################################ -->
<h1>3.3.2 The 'all' member<a name="The_all_member">&nbsp;</a></h1>

<p>By default, every hierarchy contains a top level called '<code>(All)</code>', which contains a single
member called '<code>(All {<i>hierarchyName</i>})</code>'. This member is parent of all other members
of the hierarchy, and thus represents a grand total. It is also the default member of the hierarchy; that is, the member
which is used for calculating cell values when the hierarchy is not included on an axis or in the slicer. The
<code>allMemberName</code> and <code>allLevelName</code> attributes override the default
names of the all level and all member.</p>

<p>If the <code>&lt;Hierarchy&gt;</code> element has <code> hasAll="false"</code>, the 'all'
level is suppressed. The default member of that dimension will now be the first member of the first level; for example,
in a Time hierarchy, it will be the first year in the hierarchy. Changing the default member can be confusing, so you
should generally use <code> hasAll="true"</code>.</p>

<p>The <code>&lt;Hierarchy&gt;</code> element also has a <code>defaultMember</code>
attribute, to override the default member of the hierarchy:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Dimension">Dimension</a> name=&quot;Time&quot; type=&quot;TimeDimension&quot; foreignKey=&quot;time_id&quot;&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Hierarchy">Hierarchy</a> hasAll=&quot;false&quot; primaryKey=&quot;time_id&quot; defaultMember=&quot;[Time].[1997].[Q1].[1]&quot;/&gt;</div>
                <div style="padding-left:60px;">...</div>
    </code>
</blockquote>

<!--
###############################
##  3.3.3 Time dimensions #####
############################### -->
<h1>3.3.3 Time dimensions<a name="Time_dimensions">&nbsp;</a></h1>

<p>Time dimensions based on year/month/week/day are coded differently in the Mondrian schema
due to the MDX time related functions such as:</p>

<ul>
  	<li><code>ParallelPeriod([level[, index[, member]]])</code></li>
  	<li><code>PeriodsToDate([level[, member]])</code></li>
  	<li><code>WTD([member])</code></li>
  	<li><code>MTD([member])</code></li>
  	<li><code>QTD([member])</code></li>
  	<li><code>YTD([member])</code></li>
  	<li><code>LastPeriod(index[, member])</code></li>
</ul>

<p>Time dimensions have <code>type="TimeDimension"</code>. The role of a level in a time dimension is
indicated by the level's <code>levelType</code> attribute, whose allowable values are as follows:</p>

	<table class="grayTable"  >
  		<tr>
    		<td><code>levelType</code> value</td>
    		<td><b>Meaning</b></td>
  		</tr>
  		<tr>
    		<td>TimeYears</td>
    		<td>Level is a year</td>
  		</tr>
  		<tr>
    		<td>TimeQuarters</td>
    		<td>Level is a quarter</td>
  		</tr>
  		<tr>
    		<td>TimeMonths</td>
    		<td>Level is a month</td>
  		</tr>
  		<tr>
    		<td>TimeWeeks</td>
    		<td>Level is a week</td>
  		</tr>
  		<tr>
    		<td>TimeDays</td>
    		<td>Level represents days</td>
  		</tr>
	</table>

<p>Here is an example of a time dimension:</p>

<blockquote style="text-indent: -20px">
  	<code>
		<div style="padding-left:20px;">&lt;<a href="#XML_Dimension">Dimension</a> name=&quot;Time&quot; type=&quot;TimeDimension&quot;&gt;</div>
			<div style="padding-left:40px;">&lt;<a href="#XML_Hierarchy">Hierarchy</a> hasAll=&quot;true&quot; allMemberName=&quot;All Periods&quot; primaryKey=&quot;dateid&quot;&gt;</div>
				<div style="padding-left:60px;">&lt;<a href="#XML_Table">Table</a> name=&quot;datehierarchy&quot;/&gt;</div>
				<div style="padding-left:60px;">&lt;<a href="#XML_Level">Level</a> name=&quot;Year&quot; column=&quot;year&quot; uniqueMembers=&quot;true&quot; levelType=&quot;TimeYears&quot; type=&quot;Numeric&quot;/&gt;</div>
				<div style="padding-left:60px;">&lt;<a href="#XML_Level">Level</a> name=&quot;Quarter&quot; column=&quot;quarter&quot; uniqueMembers=&quot;false&quot; levelType=&quot;TimeQuarters&quot;/&gt;</div>
				<div style="padding-left:60px;">&lt;<a href="#XML_Level">Level</a> name=&quot;Month&quot; column=&quot;month&quot; uniqueMembers=&quot;false&quot; ordinalColumn=&quot;month&quot; nameColumn=&quot;month_name&quot; levelType=&quot;TimeMonths&quot; type=&quot;Numeric&quot;/&gt;</div>
				<div style="padding-left:60px;">&lt;<a href="#XML_Level">Level</a> name=&quot;Week&quot; column=&quot;week_in_month&quot; uniqueMembers=&quot;false&quot; levelType=&quot;TimeWeeks&quot;/&gt;</div>
				<div style="padding-left:60px;">&lt;<a href="#XML_Level">Level</a> name=&quot;Day&quot; column=&quot;day_in_month&quot; uniqueMembers=&quot;false&quot; ordinalColumn=&quot;day_in_month&quot; nameColumn=&quot;day_name&quot; levelType=&quot;TimeDays&quot; type=&quot;Numeric&quot;/&gt;</div>
			<div style="padding-left:40px;">&lt;/<a href="#XML_Hierarchy">Hierarchy</a>&gt;</div>
		<div style="padding-left:20px;">&lt;/<a href="#XML_Dimension">Dimension</a>&gt;</div>
 	</code>
</blockquote>

<!--
##########################################
## 3.3.4 Order and display of levels #####
########################################## -->
<h1>3.3.4 Order and display of levels<a name="Level_Order_and_Display">&nbsp;</a></h1>

<p>Notice in the time hierarchy example above the <code>ordinalColumn</code> and
<code>nameColumn</code> attributes on the <code>&lt;Level&gt;</code> element. These
affect how levels are displayed in a result. The <code>ordinalColumn</code> attribute specifies a
column in the Hierarchy table that provides the order of the members in a given level, while the
<code>nameColumn</code> specifies a column that will be displayed.</p>

<p>For example, in the Month level above, the <code>datehierarchy</code> table has month (1 .. 12)
and month_name (January, February, ...) columns. The column value that will be used internally within MDX is the
month column, so valid member specifications will be of the form:
<code>[Time].[2005].[Q1].[<b><i>1</i></b>]</code>. Members of the <code>[Month]</code>
level will be displayed in the order January, February, etc.</p>

<p>In a parent-child hierarchy, members are always sorted in hierarchical
order. The <code>ordinalColumn</code> attribute controls the order that
siblings appear within their parent.</p>

<p>Ordinal columns may be of any datatype which can legally be used in
an ORDER BY clause.  Scope of ordering is per-parent, so in the
example above, the day_in_month column should cycle for each month.
Values returned by the JDBC driver should be non-null instances of <a
href="http://java.sun.com/j2se/1.5.0/docs/api/java/lang/Comparable.html">
java.lang.Comparable</a>
which yield the desired ordering when their
<code>Comparable.compareTo</code> method is called.</p>

<p>Levels contain a <code>type</code> attribute, which can have values "<code>String</code>", "<code>Integer</code>", "<code>Numeric</code>", &quot;<code>Boolean</code>&quot;,
&quot;<code>Date</code>&quot;, &quot;<code>Time</code>&quot;, and &quot;<code>Timestamp</code>&quot;.
The default value is <code>&quot;Numeric&quot;</code> because key columns generally have a numeric type. If it is a
different type, Mondrian needs to know this so it can generate SQL statements
correctly; for example, string values will be generated enclosed in single
quotes:</p>
<blockquote>
    <p><code>WHERE productSku = '123-455-AA'</code></p>
</blockquote>
<!--
####################################
##  3.3.5 Multiple hierarchies #####
#################################### -->
<h1>3.3.5 Multiple hierarchies<a name="Multiple_hierarchies">&nbsp;</a></h1>

<p>A dimension can contain more than one hierarchy:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Dimension">Dimension</a> name="Time" foreignKey="time_id"&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Hierarchy">Hierarchy</a> hasAll="false" primaryKey="time_id"&gt;</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_Table">Table</a> name="time_by_day"/&gt;</div>
                <div style="padding-left:60px">&lt;<a href="#XML_Level">Level</a> name="Year" column="the_year" type="Numeric" uniqueMembers="true"/&gt;</div>
                <div style="padding-left:60px">&lt;<a href="#XML_Level">Level</a> name="Quarter" column="quarter" uniqueMembers="false"/&gt;</div>
                <div style="padding-left:60px">&lt;<a href="#XML_Level">Level</a> name="Month" column="month_of_year" type="Numeric" uniqueMembers="false"/&gt;</div>
            <div style="padding-left:40px">&lt;/<a href="#XML_Hierarchy">Hierarchy</a>&gt;</div>
            <div style="padding-left:40px">&lt;<a href="#XML_Hierarchy">Hierarchy</a> name="Time Weekly" hasAll="false" primaryKey="time_id"&gt;</div>
                <div style="padding-left:60px">&lt;<a href="#XML_Table">Table</a> name="time_by_week"/&gt;</div>
                <div style="padding-left:60px">&lt;<a href="#XML_Level">Level</a> name="Year" column="the_year" type="Numeric" uniqueMembers="true"/&gt;</div>
                <div style="padding-left:60px">&lt;<a href="#XML_Level">Level</a> name="Week" column="week" uniqueMembers="false"/&gt;</div>
                <div style="padding-left:60px">&lt;<a href="#XML_Level">Level</a> name="Day" column="day_of_week" type="String" uniqueMembers="false"/&gt;</div>
            <div style="padding-left:40px">&lt;/<a href="#XML_Hierarchy">Hierarchy</a>&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_Dimension">Dimension</a>&gt;</div>
    </code>
</blockquote>

<p>Notice that the first hierarchy doesn't have a name. By default, a hierarchy has the
same name as its dimension, so the first hierarchy is called "Time".</p>

<p>These hierarchies don't have much in common -- they don't even have the same table! -- except
that they are joined from the same column in the fact table, <code>"time_id"</code>.
The main reason to put two hierarchies in the same dimension is because it makes more sense to
the end-user: end-users know that it makes no sense to have the "Time" hierarchy on one axis
and the "Time Weekly" hierarchy on another axis. If two hierarchies are the same dimension, the
MDX language enforces common sense, and does not allow you to use them both in the same query.</p>
<!--
#####################################
##  3.3.6 Degenerate dimensions #####
##################################### -->
<h1>3.3.6 Degenerate dimensions<a name="Degenerate_dimensions">&nbsp;</a></h1>

<p>A <dfn>degenerate dimension</dfn> is a dimension which is so simple that it isn't worth
creating its own dimension table. For example, consider following the fact table:</p>

  <table id="table5" class="grayTable"  >
    <tr>
      <td><b>product_id</b></td>
      <td><b>time_id</b></td>
      <td><b>payment_method</b></td>
      <td><b>customer_id</b></td>
      <td><b>store_id</b></td>
      <td><b>item_count</b></td>
      <td><b>dollars</b></td>
    </tr>

    <tr>
      <td align="right">55</td>
      <td align="right">20040106</td>
      <td>Credit</td>
      <td align="right">123</td>
      <td align="right">22</td>
      <td align="right">3</td>
      <td align="right">$3.54</td>
    </tr>
    <tr>
      <td align="right">78</td>
      <td align="right">20040106</td>
      <td>Cash</td>
      <td align="right">89</td>
      <td align="right">22</td>
      <td align="right">1</td>
      <td align="right">$20.00</td>
    </tr>
    <tr>
      <td align="right">199</td>
      <td align="right">20040107</td>
      <td>ATM</td>
      <td align="right">3</td>
      <td align="right">22</td>
      <td align="right">2</td>
      <td align="right">$2.99</td>

    </tr>
    <tr>
      <td align="right">55</td>
      <td align="right">20040106</td>
      <td>Cash</td>
      <td align="right">122</td>
      <td align="right">22</td>
      <td align="right">1</td>
      <td align="right">$1.18</td>
    </tr>
  </table>

<p>and suppose we created a dimension table for the values in the <code>payment_method</code> column:</p>

  <table id="table6" class="grayTable"  >
    <tr>
      <td><b>payment_method</b></td>
    </tr>
    <tr>
      <td>Credit</td>
    </tr>

    <tr>
      <td>Cash</td>
    </tr>
    <tr>
      <td>ATM</td>
    </tr>
  </table>

<p>This dimension table is fairly pointless. It only has 3 values, adds no additional information,
and incurs the cost of an extra join.</p>

<p>Instead, you can create a degenerate dimension. To do this, declare a dimension without a
table, and Mondrian will assume that the columns come from the fact table.</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Cube">Cube</a> name=&quot;Checkout&quot;&gt;</div>
            <div style="padding-left:40px">&lt;!-- The fact table is always necessary. --&gt;</div>
            <div style="padding-left:40px">&lt;<a href="#XML_Table">Table</a> name=&quot;checkout&quot;&gt;</div>
            <div style="padding-left:40px">&lt;<a href="#XML_Dimension">Dimension</a> name=&quot;Payment method&quot;&gt;</div>
                <div style="padding-left:60px">&lt;<a href="#XML_Hierarchy">Hierarchy</a> hasAll=&quot;true&quot;&gt;</div>
                    <div style="padding-left:80px">&lt;!-- No table element here. Fact table is assumed. --&gt;</div>
                    <div style="padding-left:80px">&lt;<a href="#XML_Level">Level</a> name=&quot;Payment method&quot; column=&quot;payment_method&quot; uniqueMembers=&quot;true&quot;/&gt;</div>
                <div style="padding-left:60px">&lt;/<a href="#XML_Hierarchy">Hierarchy</a>&gt;</div>
            <div style="padding-left:40px">&lt;/<a href="#XML_Dimension">Dimension</a>&gt;</div>
            <div style="padding-left:40px">&lt;!-- other dimensions and measures --&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_Cube">Cube</a>&gt;</div>
    </code>
</blockquote>

<p>Note that because there is no join, the <code>foreignKey</code> attribute of
<code>Dimension</code> is not necessary, and the <code>Hierarchy</code>
element has no <code>&lt;Table&gt;</code> child element or
<code>primaryKey</code> attribute.</p>
<!--
#############################
##  3.3.7 Inline tables #####
############################# -->
<h1>3.3.7 Inline tables<a name="Inline_tables">&nbsp;</a></h1>

<p>The <code>&lt;<a href="#XML_InlineTable">InlineTable</a>&gt;</code> construct allows
you to define a dataset in the schema file. You must declare the names of the columns, the column types
(&quot;String&quot; or &quot;Numeric&quot;), and a set of rows. As for
<code>&lt;<a href="#XML_Table">Table</a>&gt;</code> and
<code>&lt;<a href="#XML_View">View</a>&gt;</code>, you must provide a unique alias with which
to refer to the dataset.</p>

<p>Here is an example:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px">&lt;<a href="#XML_Dimension">Dimension</a> name="Severity"&gt;</div>
            <div style="padding-left:40px">&lt;<a href="#XML_Hierarchy">Hierarchy</a> hasAll="true" primaryKey="severity_id"&gt;</div>
                <div style="padding-left:60px">&lt;<a href="#XML_InlineTable">InlineTable</a> alias="severity"&gt;</div>
                    <div style="padding-left:80px">&lt;<a href="#XML_ColumnDefs">ColumnDefs</a>&gt;</div>
                        <div style="padding-left:100px">&lt;<a href="#XML_ColumnDef">ColumnDef</a> name=&quot;id&quot; type=&quot;Numeric&quot;/&gt;</div>
                        <div style="padding-left:100px">&lt;<a href="#XML_ColumnDef">ColumnDef</a> name=&quot;desc&quot; type=&quot;String&quot;/&gt;</div>
                    <div style="padding-left:80px">&lt;/<a href="#XML_ColumnDefs">ColumnDefs</a>&gt;</div>
                    <div style="padding-left:80px">&lt;<a href="#XML_Rows">Rows</a>&gt;</div>
                        <div style="padding-left:100px">&lt;<a href="#XML_Row">Row</a>&gt;</div>
                            <div style="padding-left:120px">&lt;<a href="#XML_Value">Value</a> column=&quot;id&quot;&gt;1&lt;/<a href="#XML_Value">Value</a>&gt;</div>
                            <div style="padding-left:120px">&lt;<a href="#XML_Value">Value</a> column=&quot;desc&quot;&gt;High&lt;/<a href="#XML_Value">Value</a>&gt;</div>
                        <div style="padding-left:100px">&lt;/<a href="#XML_Row">Row</a>&gt;</div>
                        <div style="padding-left:100px">&lt;<a href="#XML_Row">Row</a>&gt;</div>
                            <div style="padding-left:120px">&lt;<a href="#XML_Value">Value</a> column=&quot;id&quot;&gt;2&lt;/<a href="#XML_Value">Value</a>&gt;</div>
                            <div style="padding-left:120px">&lt;<a href="#XML_Value">Value</a> column=&quot;desc&quot;&gt;Medium&lt;/<a href="#XML_Value">Value</a>&gt;</div>
                        <div style="padding-left:100px">&lt;/<a href="#XML_Row">Row</a>&gt;</div>
                        <div style="padding-left:100px">&lt;<a href="#XML_Row">Row</a>&gt;</div>
                            <div style="padding-left:120px">&lt;<a href="#XML_Value">Value</a> column=&quot;id&quot;&gt;3&lt;/<a href="#XML_Value">Value</a>&gt;</div>
                            <div style="padding-left:120px">&lt;<a href="#XML_Value">Value</a> column=&quot;desc&quot;&gt;Low&lt;/<a href="#XML_Value">Value</a>&gt;</div>
                        <div style="padding-left:100px">&lt;/<a href="#XML_Row">Row</a>&gt;</div>
                    <div style="padding-left:80px">&lt;/<a href="#XML_Rows">Rows</a>&gt;</div>
                <div style="padding-left:60px">&lt;/<a href="#XML_InlineTable">InlineTable</a>&gt;</div>
                <div style="padding-left:60px">&lt;<a href="#XML_Level">Level</a> name="Severity" column="id" nameColumn=&quot;desc&quot; uniqueMembers="true"/&gt;</div>
            <div style="padding-left:40px">&lt;/<a href="#XML_Hierarchy">Hierarchy</a>&gt;</div>
        <div style="padding-left:20px">&lt;/<a href="#XML_Dimension">Dimension</a>&gt;</div>
    </code>
</blockquote>

<p>This has the same effect as if you had a table called 'severity' in your database:</p>

  <table   id="table7" class="grayTable">
    <tr>
      <td><b>id</b></td>
      <td><b>desc</b></td>
    </tr>
    <tr>
      <td>1</td>
      <td>High</td>
    </tr>
    <tr>
      <td>2</td>
      <td>Medium</td>
    </tr>
    <tr>
      <td>3</td>
      <td>Low</td>
    </tr>
  </table>

<p>and the declaration</p>

<blockquote style="text-indent: -20px">
<code>
    <div style="padding-left:20px">&lt;<a href="#XML_Dimension">Dimension</a> name="Severity"&gt;</div>
        <div style="padding-left:40px">&lt;<a href="#XML_Hierarchy">Hierarchy</a> hasAll="true" primaryKey="severity_id"&gt;</div>
            <div style="padding-left:60px">&lt;<a href="#XML_InlineTable">Table</a> name=&quot;severity&quot;/&gt;</div>
			<div style="padding-left:60px">&lt;<a href="#XML_Level">Level</a> name="Severity" column="id" nameColumn=&quot;desc&quot; uniqueMembers="true"/&gt;</div>
        <div style="padding-left:40px">&lt;/<a href="#XML_Hierarchy">Hierarchy</a>&gt;</div>
    <div style="padding-left:20px">&lt;/<a href="#XML_Dimension">Dimension</a>&gt;</div>
</code>
</blockquote>

<p>To specify a NULL value for a column, omit the <code>&lt;Value&gt;</code>
for that column, and the column's value will default to NULL.</p>
<!--
################################################
##  3.3.8 Member properties and formatters #####
################################################ -->
<h1>3.3.8 Member properties and formatters<a name="Member_properties_and_formatters">&nbsp;</a></h1>

<p>As we shall see later, a level definition can also define <a href="#Member_properties">member properties</a>
and a <a href="#Member_formatter">member formatter</a>.</p>
<!--
################################################
##  3.3.9 Approximate level cardinality #####
################################################ -->
<h1>3.3.9 Approximate level cardinality<a name="Approximate_level_cardinality">&nbsp;</a></h1>

<p>The <a
 href="#XML_Level">&lt;Level&gt;</a> element allows specifying the optional attribute "approxRowCount".  Specifying
    <code>approxRowCount</code> can improve performance by reducing the need to determine level, hierarchy, and dimension cardinality.
    This can have a significant impact when connecting to Mondrian via XMLA.</p>
<!--
################################################
##  3.3.10 Default Measure Attribute       #####
################################################ -->
<h1>3.3.10 Default Measure Attribute<a name="Default_Measure_Attribute">&nbsp;</a></h1>

<p>The <a href="#XML_Cube">&lt;Cube&gt;</a> and <a href="#XML_VirtualCube">&lt;VirtualCube&gt;</a>
elements allow specifying the optional attribute "defaultMeasure".</p>

<p>Specifying <code>defaultMeasure</code> in <a href="#XML_Cube">&lt;Cube&gt;</a> element allows users
to explicitly specify any base measure as a default Measure.</p>

<p>Specifying <code>defaultMeasure</code> in <a href="#XML_VirtualCube">&lt;VirtualCube&gt;</a>
element allows users to explicitly specify any VirtualCube Measure as a default Measure.</p>

<p>Note that if a default measure is not specified it takes the first measure defined in the cube as the default measure. In the case of virtual cube,
 it would pick up the first base measure of the first cube defined within it as the default.</p>

<p>Specifying the <code>defaultMeasure</code> explicitly would be useful in cases where you would want a calculated member to be picked up as the default measure.
 To facilitate this, the calculated member could be defined in one of the base cubes and specified as the <code>defaultMeasure</code> in the virtual cube.</p>

<blockquote style="text-indent: -20px">
<code>
    <div style="padding-left:20px">&lt;<a href="#XML_Cube">Cube</a> name=&quot;Sales&quot; defaultMeasure=&quot;Unit Sales&quot;&gt;</div>
        <div style="padding-left:40px">...</div>
        <div style="padding-left:40px">&lt;<a href="#XML_CalculatedMember">CalculatedMember</a> name=&quot;Profit&quot; dimension=&quot;Measures&quot;&gt;</div>
            <div style="padding-left:60px;">&lt;<a href="#XML_Formula">Formula</a>&gt;[Measures].[Store Sales] - [Measures].[Store Cost]&lt;/<a href="#XML_Formula">Formula</a>&gt;</div>
            <div style="padding-left:60px">...</div>
        <div style="padding-left:40px">&lt;/<span><a href="#XML_CalculatedMember">CalculatedMember</a></span>&gt;</div>
    <div style="padding-left:20px">&lt;/<a href="#XML_Cube">Cube</a>&gt;</div>
    <div style="padding-left:20px">&lt;<a href="#XML_VirtualCube">VirtualCube</a> name=&quot;Warehouse and Sales&quot; defaultMeasure=&quot;Profit&quot;&gt;</div>
        <div style="padding-left:40px">...</div>
        <div style="padding-left:40px">&lt;<a href="#XML_VirtualCubeMeasure">VirtualCubeMeasure</a> cubeName=&quot;Sales&quot; name=&quot;[Measures].[Profit]&quot;/&gt;</div>
    <div style="padding-left:20px">&lt;/<a href="#XML_VirtualCube">VirtualCube</a>&gt;</div>
</code>
</blockquote>
<!--
####################################################
##  3.3.11 Functional Dependency Optimizations #####
#################################################### -->
<h1>3.3.11 Functional Dependency Optimizations<a name="Functional_dependency_optimizations">&nbsp;</a></h1>

<p>In some circumstances, it may be possible to optimize performance by taking advantage
of known functional dependencies in the data being processed.  Such dependencies
are typically the result of business rules associated with the systems producing
the data, and often cannot be inferred just by looking at the data itself.</p>

<p>Functional dependencies are declared to Mondrian using the
<code>dependsOnLevelValue</code> attribute of the
<a href="#XML_Property">&lt;Property&gt</a> element and the
<code>uniqueKeyLevelName</code> attribute of the
<a href="#XML_Hierarchy">&lt;Hierarchy&gt</a> element.</p>

<p>The <code>dependsOnLevelValue</code> attribute of a
<a href="#Member_properties">member property</a> is used to indicate
that the value of the member property is functionally dependent on the value
of the <a href="#XML_Level">&lt;Level&gt</a> in which the member property is
defined.  In other words, for a given value of the level, the value of the
property is invariant.</p>

<p>The <code>uniqueKeyLevelName</code> attribute of a
<a href="#XML_Hierarchy">&lt;Hierarchy&gt</a> is used to indicate that the given
level (if any) taken together with all higher levels in the hierarchy acts as a
unique alternate key, ensuring that for any unique combination of those level values,
there is exactly one combination of values for all levels below it.

<p>To illustrate, consider a hierarchy modeling cars built and licensed in the
United States:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Dimension">Dimension</a> name="Automotive" foreignKey="auto_dim_id"&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Hierarchy">Hierarchy</a> hasAll="true" primaryKey="auto_dim_id" uniqueKeyLevelName="Vehicle Identification Number"&gt;</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_Table">Table</a> name="automotive_dim"/&gt;</div>
                <div style="padding-left:60px">&lt;<a href="#XML_Level">Level</a> name="Make" column="make_id" type="Numeric"/&gt;</div>
                <div style="padding-left:60px">&lt;<a href="#XML_Level">Level</a> name="Model" column="model_id" type="Numeric"/&gt;</div>
                <div style="padding-left:60px">&lt;<a href="#XML_Level">Level</a> name="ManufacturingPlant" column="plant_id" type="Numeric"/&gt;</div>
                    <div style="padding-left:80px">&lt;<a href="#XML_Property">Property</a> name="State" column="plant_state_id" type="Numeric" dependsOnLevelValue="true"/&gt;</div>
                    <div style="padding-left:80px">&lt;<a href="#XML_Property">Property</a> name="City" column="plant_city_id" type="Numeric" dependsOnLevelValue="true"/&gt;</div>
                <div style="padding-left:60px">&lt;<a href="#XML_Level">Level</a> name="Vehicle Identification Number" column="vehicle_id" type="Numeric"/&gt;</div>
                    <div style="padding-left:80px">&lt;<a href="#XML_Property">Property</a> name="Color" column="color_id" type="Numeric" dependsOnLevelValue="true"/&gt;</div>
                    <div style="padding-left:80px">&lt;<a href="#XML_Property">Property</a> name="Trim" column="trim_id" type="Numeric" dependsOnLevelValue="true"/&gt;</div>
                <div style="padding-left:60px">&lt;<a href="#XML_Level">Level</a> name="LicensePlateNum" column="license_id" type="String"/&gt;</div>
                    <div style="padding-left:80px">&lt;<a href="#XML_Property">Property</a> name="State" column="license_state_id" type="Numeric" dependsOnLevelValue="true"/&gt;</div>
            <div style="padding-left:40px">&lt;/<a href="#XML_Hierarchy">Hierarchy</a>&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_Dimension">Dimension</a>&gt;</div>
    </code>
</blockquote>

<p>In the above example, we know that a given manufacturing plant exists only in
a single city and state, that a given car only has one color scheme and
one trim level, and that the license number is associated with a single state.
Therefore, we can state that all of these member properties are functionally
dependent on the associated level values.</p>

<p>Additionally, we know that the Vehicle Identification Number uniquely identifies
each car, and that each car only has one license.  Thus, we know that the
combination of Make, Model, Manufacturing Plant, and Vehicle Identification
Number uniquely identifies each vehicle; the license number is redundant.</p>

<p>These attributes enable optimization of the GROUP BY clause in the SQL statements Mondrian generates.  Absent any
functional dependency information, a typical query on the Automotive dimension
would look something like:</p>


<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">SELECT</div>
            <div style="padding-left:40px;">`automotive_dim`.`make_id` AS c0,</div>
            <div style="padding-left:40px;">`automotive_dim`.`model_id` AS c1,</div>
            <div style="padding-left:40px;">`automotive_dim`.`plant_id` AS c2,</div>
            <div style="padding-left:40px;">`automotive_dim`.`plant_state_id` AS c3,</div>
            <div style="padding-left:40px;">`automotive_dim`.`plant_city_id` AS c4,</div>
            <div style="padding-left:40px;">`automotive_dim`.`vehicle_id` AS c5,</div>
            <div style="padding-left:40px;">`automotive_dim`.`color_id` AS c6,</div>
            <div style="padding-left:40px;">`automotive_dim`.`trim_id` AS c7,</div>
            <div style="padding-left:40px;">`automotive_dim`.`license_id` AS c8,</div>
            <div style="padding-left:40px;">`automotive_dim`.`license_state_id` AS c9</div>
        <div style="padding-left:20px;">FROM</div>
            <div style="padding-left:40px;">`automotive_dim` AS `automotive_dim`,</div>
        <div style="padding-left:20px;">GROUP BY</div>
            <div style="padding-left:40px;">`automotive_dim`.`make_id`,</div>
            <div style="padding-left:40px;">`automotive_dim`.`model_id`,</div>
            <div style="padding-left:40px;">`automotive_dim`.`plant_id`,</div>
            <div style="padding-left:40px;">`automotive_dim`.`plant_state_id`,</div>
            <div style="padding-left:40px;">`automotive_dim`.`plant_city_id`,</div>
            <div style="padding-left:40px;">`automotive_dim`.`vehicle_id`,</div>
            <div style="padding-left:40px;">`automotive_dim`.`color_id`,</div>
            <div style="padding-left:40px;">`automotive_dim`.`trim_id`,</div>
            <div style="padding-left:40px;">`automotive_dim`.`license_id`,</div>
            <div style="padding-left:40px;">`automotive_dim`.`license_state_id`</div>
        <div style="padding-left:20px;">ORDER BY</div>
            <div style="padding-left:40px;">`...</div>
    </code>
</blockquote>

<p>Given the functional dependence attributes in the schema example above, however,
we know that the query is selecting at a depth that includes the "unique key" level,
and that all properties in the query are also functionally dependent on their levels.
In such cases the GROUP BY clause is redundant and may be eliminated completely,
increasing SQL query performance significantly on some databases:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">SELECT</div>
            <div style="padding-left:40px;">`automotive_dim`.`make_id` AS c0,</div>
            <div style="padding-left:40px;">`automotive_dim`.`model_id` AS c1,</div>
            <div style="padding-left:40px;">`automotive_dim`.`plant_id` AS c2,</div>
            <div style="padding-left:40px;">`automotive_dim`.`plant_state_id` AS c3,</div>
            <div style="padding-left:40px;">`automotive_dim`.`plant_city_id` AS c4,</div>
            <div style="padding-left:40px;">`automotive_dim`.`vehicle_id` AS c5,</div>
            <div style="padding-left:40px;">`automotive_dim`.`color_id` AS c6,</div>
            <div style="padding-left:40px;">`automotive_dim`.`trim_id` AS c7,</div>
            <div style="padding-left:40px;">`automotive_dim`.`license_id` AS c8,</div>
            <div style="padding-left:40px;">`automotive_dim`.`license_state_id` AS c9</div>
        <div style="padding-left:20px;">FROM</div>
            <div style="padding-left:40px;">`automotive_dim` AS `automotive_dim`,</div>
        <div style="padding-left:20px;">ORDER BY</div>
            <div style="padding-left:40px;">`...</div>
    </code>
</blockquote>


<p>Had the query not been deep enough to include the "unique key" level, or
had any of the member properties not been functionally dependent on their level,
this optimization would not be possible.</p>

<p>In some cases, a different optimization can be made where there is no "unique key"
level, but some or all of the member properties are functionally dependent on
their level.  Some databases (notably MySQL) permit columns to be listed in the
SELECT clause that do not also appear in the GROUP BY clause.  On such databases,
Mondrian can simply leave the functionally dependent member properties out of
the GROUP BY, which may reduce SQL query processing time substantially:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">SELECT</div>
            <div style="padding-left:40px;">`automotive_dim`.`make_id` AS c0,</div>
            <div style="padding-left:40px;">`automotive_dim`.`model_id` AS c1,</div>
            <div style="padding-left:40px;">`automotive_dim`.`plant_id` AS c2,</div>
            <div style="padding-left:40px;">`automotive_dim`.`plant_state_id` AS c3,</div>
            <div style="padding-left:40px;">`automotive_dim`.`plant_city_id` AS c4,</div>
            <div style="padding-left:40px;">`automotive_dim`.`vehicle_id` AS c5,</div>
            <div style="padding-left:40px;">`automotive_dim`.`color_id` AS c6,</div>
            <div style="padding-left:40px;">`automotive_dim`.`trim_id` AS c7,</div>
            <div style="padding-left:40px;">`automotive_dim`.`license_id` AS c8,</div>
            <div style="padding-left:40px;">`automotive_dim`.`license_state_id` AS c9</div>
        <div style="padding-left:20px;">FROM</div>
            <div style="padding-left:40px;">`automotive_dim` AS `automotive_dim`,</div>
        <div style="padding-left:20px;">GROUP BY</div>
            <div style="padding-left:40px;">`automotive_dim`.`make_id`,</div>
            <div style="padding-left:40px;">`automotive_dim`.`model_id`,</div>
            <div style="padding-left:40px;">`automotive_dim`.`plant_id`,</div>
            <div style="padding-left:40px;">`automotive_dim`.`vehicle_id`,</div>
            <div style="padding-left:40px;">`automotive_dim`.`license_id`,</div>
        <div style="padding-left:20px;">ORDER BY</div>
            <div style="padding-left:40px;">`...</div>
    </code>
</blockquote>

<p>Please note that significant changes are anticipated to the schema syntax
in Mondrian 4.0, including a new approach to declaring functional dependencies.
While the expectation is that the 4.0 schema processor will maintain backward
compatibility with schemas developed for Mondrian 3.1, these are transitional
attributes introduced to allow support in the interim, and 4.0 will not be
backward compatible with them. Therefore, any schema using these attributes will
need to be migrated to the new syntax as part of upgrading to Mondrian 4.0.</p>

<!--
####################################################
##  3.3.12 Table Hints #####
#################################################### -->
<h1>3.3.12 Table Hints<a name="Table_hints">&nbsp;</a></h1>

<p>Mondrian supports a limited set of database-specific hints for the
<a href="#XML_Table">&lt;Table&gt</a> element, which will then be
passed on to SQL queries involving the table.  These hints are as
follows:</p>

	<table class="grayTable" id="table12" width="500">
  		<tr>
    		<td><b>Database</b></td>
    		<td><b>Hint Type</b></td>
    		<td><b>Permitted Values</b></td>
    		<td><b>Description</b></td>
  		</tr>
  		<tr>
    		<td>MySQL</td>
    		<td><code>force_index</code></td>
    		<td>The name of an index on this table</td>
    		<td>Forces the named index to be used when selecting level values
            from this table.</td>
  		</tr>
  		</table>

<p>For example:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Table">Table</a> name="automotive_dim"&gt;</div>
            <div style="padding-left:40px">&lt;<a href="#XML_Hint">Hint</a> type="force_index"&gt;my_index&lt;/<a href="#XML_Hint">Hint</a>&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_Table">Table</a>&gt;</div>
    </code>
</blockquote>

<p>As with the functional dependency optimizations, support for table hints
is in a transitional stage, and are likely to change in Mondrian 4.0.  Any
schema using them may need to be migrated to the new schema syntax as part of
upgrading to Mondrian 4.0.</p>

<!--
#######################################
##  4. Star and snowflake schemas #####
####################################### -->
<h3>4. Star and snowflake schemas<a name="Star_schemas">&nbsp;</a></h3>

<p>We saw earlier how to build a cube based upon a fact table, and dimensions in the fact
table ("Payment method") and in a table joined to the fact table ("Gender"). This is the
most common kind of mapping, and is known as a <dfn>star schema</dfn>.</p>

<p>But a dimension can be based upon more than one table, provided that there is a well-defined
path to join these tables to the fact table. This kind of dimension is known as a snowflake,
and is defined using the <code>&lt;<a href="#XML_Join">Join</a>&gt;</code> operator. For example:</p>

<blockquote style="text-indent: -20px;">
<code>
    <div style="padding-left:20px;">&lt;<a href="#XML_Cube">Cube</a> name=&quot;Sales&quot;&gt;</div>
        <div style="padding-left:40px;">...</div>
        <div style="padding-left:40px;">&lt;<a href="#XML_Dimension">Dimension</a> name=&quot;Product&quot; foreignKey=&quot;product_id&quot;&gt;</div>
			<div style="padding-left:60px;">&lt;<a href="#XML_Hierarchy">Hierarchy</a> hasAll=&quot;true&quot; primaryKey=&quot;product_id&quot; primaryKeyTable=&quot;product&quot;&gt;</div>
                <div style="padding-left:80px;">&lt;<a href="#XML_Join">Join</a> leftKey=&quot;product_class_key&quot; rightAlias=&quot;product_class&quot; rightKey=&quot;product_class_id&quot;&gt;</div>
				    <div style="padding-left:100px;">&lt;<a href="#XML_Table">Table</a> name=&quot;product&quot;/&gt;</div>
    				<div style="padding-left:100px;">&lt;<a href="#XML_Join">Join</a> leftKey=&quot;product_type_id&quot; rightKey=&quot;product_type_id&quot;&gt;</div>
                        <div style="padding-left:120px;">&lt;<a href="#XML_Table">Table</a> name=&quot;product_class&quot;/&gt;</div>
                        <div style="padding-left:120px;">&lt;<a href="#XML_Table">Table</a> name=&quot;product_type&quot;/&gt;</div>
				    <div style="padding-left:100px;">&lt;/<a href="#XML_Join">Join</a>&gt;</div>
                <div style="padding-left:80px;">&lt;/<a href="#XML_Join">Join</a>&gt;</div>
                <div style="padding-left:80px">&lt;!-- Level declarations ... --&gt;</div>
            <div style="padding-left:60px">&lt;/<a href="#XML_Hierarchy">Hierarchy</a>&gt;</div>
        <div style="padding-left:40px;">&lt;/<a href="#XML_Dimension">Dimension</a>&gt;</div>
    <div style="padding-left:20px;">&lt;/<a href="#XML_Cube">Cube</a>&gt;</div>
</code>
</blockquote>

<p>This defines a <code>"Product"</code> dimension consisting of three tables. The
fact table joins to <code>"product"</code> (via the foreign key <code>
"product_id"</code>), which joins to <code>"product_class"</code> (via the foreign
key <code>"product_class_id"</code>), which joins to <code>"
product_type"</code> (via the foreign key <code>"product_type_id"</code>). We require
a <code>&lt;Join&gt;</code> element nested within a <code>&lt;Join&gt;
</code> element because <code>&lt;Join&gt;</code> takes two operands; the operands
can be tables, joins, or even queries.</p>

<p>The arrangement of the tables seems complex; the simple rule of thumb is to order the tables
by the number of rows they contain. The <code>"product"</code> table has the most
rows, so it joins to the fact table and appears first; <code>"product_class"</code>
has fewer rows, and <code>"product_type"</code>, at the tip of the snowflake, has
least of all.</p>

<p>Note that the outer <code>&lt;Join&gt;</code> element has a <code>
rightAlias</code> attribute. This is necessary because the right component of the join (the inner
<code>&lt;Join&gt;</code> element) consists of more than one table. No
<code>leftAlias</code> attribute is necessary in this case, because the leftKey
column unambiguously comes from the <code>"product"</code> table.</p>
<!--
###############################
##  4.1 Shared dimensions #####
############################### -->
<h1>4.1 Shared dimensions<a name="Shared_dimensions">&nbsp;</a></h1>

<p>When generating the SQL for a join, Mondrian needs to know which column to join to. If you are
joining to a join, then you need to tell it which of the tables in the join that column belongs
to (usually it will be the first table in the join).</p>

<p>Because shared dimensions don't belong to a cube, you have to give them an explicit table
(or other data source). When you use them in a particular cube, you specify the foreign key. This
example shows the <code>Store Type</code> dimension being joined to the
<code>Sales</code> cube using the <code>sales_fact_1997.store_id
</code> foreign key, and to the <code>Warehouse</code> cube using the
<code>warehouse.warehouse_store_id</code> foreign key:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Dimension">Dimension</a> name="Store Type"&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Hierarchy">Hierarchy</a> hasAll="true" primaryKey="store_id"&gt;</div>
			    <div style="padding-left:60px">&lt;<a href="#XML_Table">Table</a> name="store"/&gt;</div>
			    <div style="padding-left:60px">&lt;<a href="#XML_Level">Level</a> name="Store Type" column="store_type" uniqueMembers="true"/&gt;</div>
            <div style="padding-left:40px;">&lt;/<a href="#XML_Hierarchy">Hierarchy</a>&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_Dimension">Dimension</a>&gt;</div>
    <br/>
        <div style="padding-left:20px;">&lt;<a href="#XML_Cube">Cube</a> name="Sales"&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Table">Table</a> name="sales_fact_1997"/&gt;</div>
            <div style="padding-left:40px;">...</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_DimensionUsage">DimensionUsage</a> name="Store Type" source="Store Type" foreignKey="store_id"/&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_Cube">Cube</a>&gt;</div>
    <br/>
        <div style="padding-left:20px;">&lt;<a href="#XML_Cube">Cube</a> name="Warehouse"&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Table">Table</a> name="warehouse"/&gt;</div>
            <div style="padding-left:40px;">...</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_DimensionUsage">DimensionUsage</a> name="Store Type" source="Store Type" foreignKey="warehouse_store_id"/&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_Cube">Cube</a>&gt;</div>
    </code>
</blockquote>

<!--
###############################
##  4.2 Join optimization #####
############################### -->
<h1>4.2 Join optimization<a name="Join_optimization">&nbsp;</a></h1>

<p>The table mapping in the schema tells Mondrian how to get the data, but Mondrian is smart
enough not to read the schema literally. It applies a number of optimizations when generating
queries:</p>

<ul>
  	<li>TODO: describe large dimension support</li>
  	<li>If a dimension (or, more precisely, the level of the dimension being accessed) is in the fact table, Mondrian does
	not perform a join.</li>
  	<li>If two dimensions access the same table via the same join path, Mondrian only joins them once. For example,
	<code>[Gender]</code> and <code>[Age]</code> might both be columns in the
	<code>customers</code> table, joined via <code>sales_1997.cust_id = customers.cust_id</code>.</li>
</ul>

<!--
########################################
##  5. Advanced logical constructs #####
######################################## -->
<h3>5. Advanced logical constructs<a name="Advanced_logical_constructs">&nbsp;</a></h3>

<!--
###########################
##  5.1 Virtual cubes #####
########################### -->
<h1>5.1 Virtual cubes<a name="Virtual_cubes">&nbsp;</a></h1>

<p>A virtual cube combines two or more regular cubes. It is defined by the <code><a href="#XML_VirtualCube">&lt;VirtualCube&gt;</a></code>
element:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_VirtualCube">VirtualCube</a> name=&quot;Warehouse and Sales&quot;&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_CubeUsages">CubeUsages</a>&gt;</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_CubeUsage">CubeUsage</a> cubeName=&quot;Sales&quot; ignoreUnrelatedDimensions=&quot;true&quot;/&gt;</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_CubeUsage">CubeUsage</a> cubeName=&quot;Warehouse&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;/<a href="#XML_CubeUsages">CubeUsages</a>&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_VirtualCubeDimension">VirtualCubeDimension</a> cubeName=&quot;Sales&quot; name=&quot;Customers&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_VirtualCubeDimension">VirtualCubeDimension</a> cubeName=&quot;Sales&quot; name=&quot;Education Level&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_VirtualCubeDimension">VirtualCubeDimension</a> cubeName=&quot;Sales&quot; name=&quot;Gender&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_VirtualCubeDimension">VirtualCubeDimension</a> cubeName=&quot;Sales&quot; name=&quot;Marital Status&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_VirtualCubeDimension">VirtualCubeDimension</a> name=&quot;Product&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_VirtualCubeDimension">VirtualCubeDimension</a> cubeName=&quot;Sales&quot; name=&quot;Promotion Media&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_VirtualCubeDimension">VirtualCubeDimension</a> cubeName=&quot;Sales&quot; name=&quot;Promotions&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_VirtualCubeDimension">VirtualCubeDimension</a> name=&quot;Store&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_VirtualCubeDimension">VirtualCubeDimension</a> name=&quot;Time&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_VirtualCubeDimension">VirtualCubeDimension</a> cubeName=&quot;Sales&quot; name=&quot;Yearly Income&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_VirtualCubeDimension">VirtualCubeDimension</a> cubeName=&quot;Warehouse&quot; name=&quot;Warehouse&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_VirtualCubeMeasure">VirtualCubeMeasure</a> cubeName=&quot;Sales&quot; name=&quot;[Measures].[Sales Count]&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_VirtualCubeMeasure">VirtualCubeMeasure</a> cubeName=&quot;Sales&quot; name=&quot;[Measures].[Store Cost]&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_VirtualCubeMeasure">VirtualCubeMeasure</a> cubeName=&quot;Sales&quot; name=&quot;[Measures].[Store Sales]&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_VirtualCubeMeasure">VirtualCubeMeasure</a> cubeName=&quot;Sales&quot; name=&quot;[Measures].[Unit Sales]&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_VirtualCubeMeasure">VirtualCubeMeasure</a> cubeName=&quot;Sales&quot; name=&quot;[Measures].[Profit Growth]&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_VirtualCubeMeasure">VirtualCubeMeasure</a> cubeName=&quot;Warehouse&quot; name=&quot;[Measures].[Store Invoice]&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_VirtualCubeMeasure">VirtualCubeMeasure</a> cubeName=&quot;Warehouse&quot; name=&quot;[Measures].[Supply Time]&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_VirtualCubeMeasure">VirtualCubeMeasure</a> cubeName=&quot;Warehouse&quot; name=&quot;[Measures].[Units Ordered]&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_VirtualCubeMeasure">VirtualCubeMeasure</a> cubeName=&quot;Warehouse&quot; name=&quot;[Measures].[Units Shipped]&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_VirtualCubeMeasure">VirtualCubeMeasure</a> cubeName=&quot;Warehouse&quot; name=&quot;[Measures].[Warehouse Cost]&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_VirtualCubeMeasure">VirtualCubeMeasure</a> cubeName=&quot;Warehouse&quot; name=&quot;[Measures].[Warehouse Profit]&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_VirtualCubeMeasure">VirtualCubeMeasure</a> cubeName=&quot;Warehouse&quot; name=&quot;[Measures].[Warehouse Sales]&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_VirtualCubeMeasure">VirtualCubeMeasure</a> cubeName=&quot;Warehouse&quot; name=&quot;[Measures].[Average Warehouse Sale]&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;<span><a href="#XML_CalculatedMember">CalculatedMember</a></span> name=&quot;Profit Per Unit Shipped&quot; dimension=&quot;Measures&quot;&gt;</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_Formula">Formula</a>&gt;[Measures].[Profit] / [Measures].[Units Shipped]&lt;/<a href="#XML_Formula">Formula</a>&gt;</div>
            <div style="padding-left:40px;">&lt;/<span><a href="#XML_CalculatedMember">CalculatedMember</a></span>&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_VirtualCube">VirtualCube</a>&gt;</div>
    </code>
</blockquote>

<p>The <code>&lt;<a href="#XML_CubeUsages">CubeUsages</a>&gt;</code>
element is optional. It specifies the cubes that are imported into the virtual cube.
Holds CubeUsage elements.</p>

<p>The <code>&lt;<a href="#XML_CubeUsage">CubeUsage</a>&gt;</code>
element is optional. It specifies the base cube that is imported into the
virtual cube. Currently it is possible to define a VirtualCubeMeasure and
similar imports from a base cube without defining CubeUsage for the cube.
The <code>cubeName</code> attribute specifies the base cube being imported.
The <code>ignoreUnrelatedDimensions</code> attribute specifies that the measures
from this base cube will have non joining dimension members pushed to the
top level member. This behaviour is currently supported for aggregation.
This attribute is by default false.
<code>ignoreUnrelatedDimensions</code> is an experimental feature similar to
the similarly named feature in SSAS 2005. 
<a href="http://msdn2.microsoft.com/en-us/library/ms128498.aspx">MSDN documentation</a>
mentions "When IgnoreUnrelatedDimensions is true, unrelated dimensions are forced
to their top level; when the value is false, dimensions are not forced to their
top level. This property is similar to the Multidimensional Expressions
(MDX) ValidMeasure function". Current Mondrian implementation of
<code>ignoreUnrelatedDimensions</code> depends on use of ValidMeasure. E.g. If we
want to apply this behaviour to "Unit Sales" measure in the "Warehouse and Sales"
virtual cube then we need to define a CubeUsage entry for "Sales" cube as shown
in the example above and also wrap this measure with ValidMeasure.</p>

<p>The <code>&lt;<a href="#XML_VirtualCubeDimension">VirtualCubeDimension</a>&gt;</code>
element imports a dimension from one of the constituent cubes. If you do not
specify the <code>cubeName</code> attribute, this means you
are importing a shared dimension. (If a shared dimension is used more than once
in a cube, there is no way, at present, to disambiguate which usage of the
shared dimension you intend to import.)</p>

<p>The <code>&lt;<a href="#XML_VirtualCubeMeasure">VirtualCubeMeasure</a>&gt;</code>
element imports a measure from one of the constituent cubes. It is imported with
the same name. If you want to create a formula, or just to rename a measure as
you import it, use the <code>&lt;<span><a href="#XML_CalculatedMember">CalculatedMember</a>&gt;</span></code>
element.</p>

<p>Virtual cubes occur surprisingly frequently in real-world applications. They
occur when you have fact tables of different granularities (say one measured at
the day level, another at the month level), or fact tables of different
dimensionalities (say one on Product, Time and Customer, another on Product,
Time and Warehouse), and want to present the results to an end-user who doesn't
know or care how the data is structured.</p>

<p>Any common dimensions -- shared dimensions which are used by both constituent
cubes -- are automatically synchronized. In this example, <code>[Time]</code>
and <code>[Product]</code> are common dimensions. So if the context is (<code>[Time].[1997].[Q2]</code>,
<code>[Product].[Beer].[Miller Lite]</code>), measures from either cube will
relate to this context.</p>

<p>Dimensions which only belong to one cube are called non-conforming
dimensions. The <code>[Gender]</code> dimension is an example of this: it exists
in the <code>Sales</code> cube but not <code>Warehouse</code>. If the context is
(<code>[Gender].[F]</code>, <code>[Time].[1997].[Q1]</code>), it makes sense to
ask the value of the <code>[Unit Sales]</code> measure (which comes from the
<code>[Sales]</code> cube) but not the <code>[Units Ordered]</code> measure (from <code>
[Warehouse]</code>). In the context of <code>[Gender].[F]</code>, <code>[Units
Ordered]</code> has value NULL.</p>

<!--
######################################
##  5.2 Parent-child hierarchies #####
###################################### -->

<h1>5.2 Parent-child hierarchies<a name="Parent_child_hierarchies">&nbsp;</a></h1>

<p>A conventional hierarchy has a rigid set of levels, and members which adhere to those
levels. For example, in the <code>Product</code> hierarchy, any member of the <code>Product Name
</code> level has a parent in the <code>Brand Name</code> level, which has a parent in the
<code>Product Subcategory</code> level, and so forth. This structure is sometimes too rigid
to model real-world data.</p>

<p>A <dfn>parent-child hierarchy</dfn> has only one level (not counting the special 'all' level),
but any member can have parents in the same level. A classic example is the reporting structure
in the <code>Employees</code> hierarchy:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Dimension">Dimension</a> name="Employees" foreignKey="employee_id"&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Hierarchy">Hierarchy</a> hasAll="true" allMemberName="All Employees" primaryKey="employee_id"&gt;</div>
			    <div style="padding-left:60px">&lt;<a href="#XML_Table">Table</a> name="employee"/&gt;</div>
			    <div style="padding-left:60px">&lt;<a href="#XML_Level">Level</a> name="Employee Id" uniqueMembers="true" type="Numeric" column="employee_id" nameColumn="full_name" parentColumn="supervisor_id" nullParentValue="0"&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_Property">Property</a> name="Marital Status" column="marital_status"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_Property">Property</a> name="Position Title" column="position_title"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_Property">Property</a> name="Gender" column="gender"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_Property">Property</a> name="Salary" column="salary"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_Property">Property</a> name="Education Level" column="education_level"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_Property">Property</a> name="Management Role" column="management_role"/&gt;</div>
                <div style="padding-left:60px">&lt;/<a href="#XML_Level">Level</a>&gt;</div>
            <div style="padding-left:40px;">&lt;/<a href="#XML_Hierarchy">Hierarchy</a>&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_Dimension">Dimension</a>&gt;</div>
    </code>
</blockquote>

<p>The important attributes here are <code>parentColumn</code> and <code>nullParentValue</code>:</p>

<ul>
  <li>The <code>parentColumn</code> attribute is the name of the
column which links a member to its parent member; in this case, it is
the foreign key column which points to an employee's supervisor. The <code>&lt;ParentExpression&gt;</code>

child element of <code>&lt;Level&gt;</code> is equivalent to the <code>parentColumn</code>
attribute, but allows you to define an arbitrary SQL expression, just
like the <code>&lt;Expression&gt;</code> element. The <code>parentColumn</code>
attribute (or <code>&lt;ParentExpression&gt;</code> element) is the
only indication to Mondrian that a hierarchy has a parent-child
structure.</li>

  <li>The <code>nullParentValue</code> attribute is the value which
indicates that a member has no parent. The default is <code>nullParentValue="null"</code>,
but since many database don't index null values, schema designers
sometimes use values as the empty string, 0, and -1 instead.</li>
</ul>
<!--
###############################################
##  5.2.1 Tuning parent-child hierarchies #####
############################################### -->
<h1>5.2.1 Tuning parent-child hierarchies<a name="Tuning_parent-child_hierarchies">&nbsp;</a></h1>

<p>There's one serious problem with the parent-child hierarchy defined above, and that is the
amount of work Mondrian has to do in order to compute cell-totals. Let's suppose that the
employee table contains the following data:</p>

  <table id="table3.5"   class="grayTable">
      <tr>
        <td colspan="3"><b>employee</b></td>
      </tr>
      <tr>
        <td><b>supervisor_id</b></td>
        <td><b>employee_id</b></td>
        <td><b>full_name</b></td>
      </tr>
      <tr>
        <td>null</td>
        <td>1</td>
        <td>Frank</td>
      </tr>
      <tr>
        <td>1</td>
        <td>2</td>
        <td>Bill</td>
      </tr>
      <tr>
        <td>2</td>
        <td>3</td>
        <td>Eric</td>
      </tr>
      <tr>
        <td>1</td>
        <td>4</td>
        <td>Jane</td>
      </tr>
      <tr>
        <td>3</td>
        <td>5</td>
        <td>Mark</td>
      </tr>
      <tr>
        <td>2</td>
        <td>6</td>
        <td>Carla</td>
      </tr>
  </table>

<p>If we want to compute the total salary budget for Bill, we need to add in the salaries of Eric
and Carla (who report to Bill) and Mark (who reports to Eric). Usually Mondrian generates a
SQL <code>GROUP BY</code> statement to compute these totals, but there is no
(generally available) SQL construct which can traverse hierarchies. So by default,
Mondrian generates one SQL statement per supervisor, to retrieve and total all of that
supervisor's direct reports.</p>

<p>This approach has a couple of drawbacks. First, the performance is not very good if a
hierarchy contains more than a hundred members. Second, because Mondrian implements the
distinct-count aggregator by generating SQL, you cannot define a distinct-count
measure in
any cube which contains a parent-child hierarchy.</p>

<p>How can we solve these problems? The answer is to enhance the data so that Mondrian is
able to retrieve the information it needs using standard SQL. Mondrian supports a mechanism
called a <dfn>closure table</dfn> for this purpose.</p>
<!--
##############################
##  5.2.2 Closure tables #####
############################## -->
<h1>5.2.2 Closure tables<a name="Closure_tables">&nbsp;</a></h1>

<p>A closure table is a SQL table which contains a record for every employee/supervisor
relationship, regardless of depth. (In mathematical terms, this is called the 'reflexive
transitive closure' of the employee/supervisor relationship. The <code>distance</code>
column is not strictly required, but it makes it easier to populate the table.)</p>

  <table id="table3"   class="grayTable">
      <tr>
        <td colspan="3"><strong>employee_closure</strong></td>
    </tr>
      <tr>
        <td width="91"><strong>supervisor_id</strong></td>
        <td width="85"><strong>employee_id</strong></td>
        <td width="8"><strong>distance</strong></td>
      </tr>
      <tr>
        <td width="91">1</td>
        <td width="85">1</td>
        <td width="8">0</td>
      </tr>
      <tr>
        <td width="91">1</td>
        <td width="85">2</td>
        <td width="8">1</td>
      </tr>
      <tr>
        <td width="91">1</td>
        <td width="85">3</td>
        <td width="8">2</td>
      </tr>
      <tr>
        <td width="91">1</td>
        <td width="85">4</td>
        <td width="8">1</td>
      </tr>
      <tr>
        <td width="91">1</td>
        <td width="85">5</td>
        <td width="8">3</td>
      </tr>
      <tr>
        <td width="91">1</td>
        <td width="85">6</td>
        <td width="8">2</td>
      </tr>
      <tr>
        <td width="91">2</td>
        <td width="85">2</td>
        <td width="8">0</td>
      </tr>
      <tr>
        <td width="91">2</td>
        <td width="85">3</td>
        <td width="8">1</td>
      </tr>
      <tr>
        <td width="91">2</td>
        <td width="85">5</td>
        <td width="8">2</td>
      </tr>
      <tr>
        <td width="91">2</td>
        <td width="85">6</td>
        <td width="8">1</td>
      </tr>
      <tr>
        <td width="91">3</td>
        <td width="85">3</td>
        <td width="8">0</td>
      </tr>
      <tr>
        <td width="91">3</td>
        <td width="85">5</td>
        <td width="8">1</td>
      </tr>
      <tr>
        <td width="91">4</td>
        <td width="85">4</td>
        <td width="8">0</td>
      </tr>
      <tr>
        <td width="91">5</td>
        <td width="85">5</td>
        <td width="8">0</td>
      </tr>
      <tr>
        <td width="91">6</td>
        <td width="85">6</td>
        <td width="8">0</td>
      </tr>
  </table>

<p>In the catalog XML, the <code>&lt;<a href="#XML_Closure">Closure</a>&gt;</code> element maps
the level onto a <code>&lt;<a href="#XML_Table">Table</a>&gt;</code>:</p>

<blockquote>
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Dimension">Dimension</a> name="Employees" foreignKey="employee_id"&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Hierarchy">Hierarchy</a> hasAll="true" allMemberName="All Employees" primaryKey="employee_id"&gt;</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_Table">Table</a> name="employee"/&gt;</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_Level">Level</a> name="Employee Id" uniqueMembers="true" type="Numeric"<br/> column="employee_id" nameColumn="full_name" parentColumn="supervisor_id" nullParentValue="0"&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_Closure">Closure</a> parentColumn="supervisor_id" childColumn="employee_id"&gt;</div>
				        <div style="padding-left:100px;">&lt;<a href="#XML_Table">Table</a> name="employee_closure"/&gt;</div>
                    <div style="padding-left:80px;">&lt;/<a href="#XML_Closure">Closure</a>&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_Property">Property</a> name="Marital Status" column="marital_status"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_Property">Property</a> name="Position Title" column="position_title"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_Property">Property</a> name="Gender" column="gender"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_Property">Property</a> name="Salary" column="salary"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_Property">Property</a> name="Education Level" column="education_level"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_Property">Property</a> name="Management Role" column="management_role"/&gt;</div>
                <div style="padding-left:60px;">&lt;/<a href="#XML_Level">Level</a>&gt;</div>
            <div style="padding-left:40px;">&lt;/<a href="#XML_Hierarchy">Hierarchy</a>&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_Dimension">Dimension</a>&gt;</div>
    </code>
</blockquote>

<p>This table allows totals to be evaluated in pure SQL. Even though this introduces an extra
table into the query, database optimizers are very good at handling joins. I recommend that
you declare both <code>supervisor_id</code> and <code>employee_id</code> NOT NULL, and index
them as follows:</p>

<blockquote>
  <code>CREATE UNIQUE INDEX employee_closure_pk ON employee_closure (<br/>
&nbsp;&nbsp; supervisor_id,<br/>
&nbsp;&nbsp; employee_id);<br/>
CREATE INDEX employee_closure_emp ON employee_closure (<br/>
&nbsp;&nbsp; employee_id);</code>
</blockquote>

<h1>5.2.3 Populating closure tables<a name="Populating_closure_tables">&nbsp;</a></h1>

<p>The table needs to be re-populated whenever the hierarchy changes, and it is 
the application's responsibility to do so &mdash; Mondrian does not do this!</p>

<p>If you are using <a href="http://kettle.pentaho.com/">Pentaho Data Integration (Kettle)</a>, there is a special step 
to populate closure tables as part of the ETL process. Further details in the
<a href="http://wiki.pentaho.com/display/EAI/Closure+Generator">Pentaho Data 
Integration wiki</a>.</p>

<p/>
<table cellspacing="4" border="1" style="border-collapse: collapse">
	<tr>
		<td>
		<img
            border="0"
            src="http://wiki.pentaho.com/download/attachments/1050777/closure-generator-step.png"
            alt="Closure Generator step in Pentaho Data Integration"
            width="474"
            height="218">
        <p align="center">
		<i>Closure Generator step in Pentaho Data Integration</i>
        </p>
        </td>
	</tr>
</table>

<p>If you are not using Pentaho Data Integration, you can populate the table 
yourself using SQL. Here is an example of a MySQL stored procedure that populates a closure table.</p>

<blockquote>
  <code>DELIMITER //<br/>
	<br/>
	CREATE PROCEDURE populate_employee_closure()<br/>
BEGIN<br/>
&nbsp; DECLARE distance int;<br/>
&nbsp; TRUNCATE TABLE employee_closure;<br/>
&nbsp; SET distance = 0;<br/>
&nbsp; <i>-- seed closure with self-pairs (distance 0)</i><br/>
&nbsp; INSERT INTO employee_closure (supervisor_id, employee_id, distance)<br/>
&nbsp;&nbsp;&nbsp; SELECT employee_id, employee_id, distance<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM employee;<br/>
  <br/>
&nbsp; <i>-- for each pair (root, leaf) in the closure,<br/>
&nbsp; -- add (root, leaf-&gt;child) from the base table</i><br/>
&nbsp; REPEAT<br/>
&nbsp;&nbsp;&nbsp; SET distance = distance + 1;<br/>
&nbsp;&nbsp;&nbsp; INSERT INTO employee_closure (supervisor_id, employee_id, distance)<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT employee_closure.supervisor_id, employee.employee_id, distance<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM employee_closure, employee<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE employee_closure.employee_id = employee.supervisor_id<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND employee_closure.distance = distance - 1;<br/>
&nbsp; UNTIL (ROW_COUNT() == 0))<br/>
&nbsp; END REPEAT;<br/>
END //<br/>
	<br/>
	DELIMITER ;</code>
</blockquote>
<!--
###############################
##  5.3 Member properties #####
############################### -->
<h1>5.3 Member properties<a name="Member_properties">&nbsp;</a></h1>

<p>Member properties are defined by the <code><a href="#XML_Property">&lt;Property&gt;</a></code>
element within a <code>&lt;<a href="#XML_Level">Level</a>&gt;</code>, like this:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Level">Level</a> name="MyLevel" column="LevelColumn" uniqueMembers="true"&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Property">Property</a> name="MyProp" column="PropColumn" formatter="com.example.MyPropertyFormatter"/&gt;</div>
        <div style="padding-left:20px;">&lt;<a href="#XML_Level">Level</a>/&gt;</div>
    </code>
</blockquote>

<p>The <code>formatter</code> attribute defines a <a href="#Property_formatter">property formatter
</a>, which is explained later.</p>

<p>Once properties have been defined in the schema, you can use them in MDX statements via the
<code><i>member</i>.Properties("<i>propertyName</i>")</code> function, for example:</p>

<blockquote>
<code>SELECT {[Store Sales]} ON COLUMNS,<br/>
&nbsp; TopCount(Filter([Store].[Store Name].Members,<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
[Store].CurrentMember.Properties("Store Type") = "Supermarket"),<br/>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10,<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Store
Sales]) ON ROWS<br/>
FROM [Sales]</code>
</blockquote>

<p>Mondrian deduces the type of the property expression, if it can. If the property name is a
constant string, the type is based upon the <code>type</code> attribute ("String", "Numeric" or "Boolean")
of the property definition. If the property name is an expression (for example
<code>CurrentMember.Properties("Store " + "Type")</code>), Mondrian will return an untyped
value.</p>
<!--
################################
##  5.4 Calculated members #####
################################ -->
<h1>5.4 Calculated members<a name="Calculated_members">&nbsp;</a></h1>

<p>Suppose you want to create a measure whose value comes not from a column of the fact table,
but from an MDX formula. One way to do this is to use a <code>WITH MEMBER</code> clause, like
this:</p>

<blockquote>
  <code>WITH MEMBER [Measures].[Profit] AS '[Measures].[Store
Sales]-[Measures].[Store Cost]',<br/>
&nbsp;&nbsp; FORMAT_STRING = '$#,###'<br/>
SELECT {[Measures].[Store Sales], [Measures].[Profit]} ON COLUMNS,<br/>
&nbsp; {[Product].Children} ON ROWS<br/>
FROM [Sales]<br/>
WHERE [Time].[1997]</code>
</blockquote>

<p>But rather than including this clause in every MDX query of your application, you can define
the member in your schema, as part of your cube definition:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_CalculatedMember">CalculatedMember</a> name="Profit" dimension="Measures"&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Formula">Formula</a>&gt;[Measures].[Store Sales] - [Measures].[Store Cost]&lt;/<a href="#XML_Formula">Formula</a>&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_CalculatedMemberProperty">CalculatedMemberProperty</a> name="FORMAT_STRING" value="$#,##0.00"/&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_CalculatedMember">CalculatedMember</a>&gt;</div>
    </code>
</blockquote>

<p>You can also declare the formula as an XML attribute, if you prefer. The effect is just the same.</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_CalculatedMember">CalculatedMember</a> name="Profit" dimension="Measures" formula="[Measures].[Store Sales]-[Measures].[Store Cost]"&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_CalculatedMemberProperty">CalculatedMemberProperty</a> name="FORMAT_STRING" value="$#,##0.00"/&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_CalculatedMember">CalculatedMember</a>&gt;</div>
    </code>
</blockquote>

<p>Note that the <code>&lt;<a href="#XML_CalculatedMemberProperty">CalculatedMemberProperty</a>&gt;</code>
(not <code>&lt;<a href="#XML_Property">Property</a>&gt;</code>) element corresponds
to the <code>FORMAT_STRING = '$#,###'</code> fragment of the MDX statement. You can define
other properties here too, but <code>FORMAT_STRING</code> is by far the most useful in practice.</p>

<p>The <code>FORMAT_STRING</code> property value can also be evaluated using an expression.
When formatting a particular cell, first the expression is evaluated to yield a format string,
then the format string is applied to the cell value. Here is the same property with a conditional
format string:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_CalculatedMemberProperty">CalculatedMemberProperty</a> name="FORMAT_STRING" expression="Iif(Value &lt; 0, '|($#,##0.00)|style=red', '|$#,##0.00|style=green')"/&gt;</div>
    </code>
</blockquote>

<p>For more details about format strings, see the
<a href="mdx.html#Format_strings">MDX specification</a>.</p>

<p>One additional calculated member property that is worth mentioning is DATATYPE.
As with <a href="#Measures">measures</a>,
setting datatype specifies how the calculated member is returned via XML for Analysis.
The DATATYPE property of a calculated member can have values "<code>String</code>", "<code>Integer</code>", or
"<code>Numeric</code>":
</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_CalculatedMemberProperty">CalculatedMemberProperty</a> name="DATATYPE" value="Numeric"/&gt;</div>
    </code>
</blockquote>

<p>You can specify SOLVE_ORDER for the calculated member property. Solve order
    determines the priority of calculation in the event of competing expressions</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_CalculatedMemberProperty">CalculatedMemberProperty</a> name="SOLVE_ORDER" value="2000"/&gt;</div>
    </code>
</blockquote>

<p>You can make a calculated member or a measure invisible. If you specify <code>visible="false"</code>
(the default is "true") in the <code>&lt;<a href="#XML_Measure">Measure</a>&gt;</code> or
<code>&lt;<a href="#XML_CalculatedMember">CalculatedMember</a>&gt;</code> element, user-interfaces such as
JPivot will notice this property and hide the member. This is useful if you want to perform
calculations in a number of steps, and hide intermediate steps from end-users. For example,
here only "Margin per Sqft" is visible, and its factors "Store Cost", "Margin" and "Store Sqft"
are hidden:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Measure">Measure</a> name="Store Cost" column="store_cost" aggregator="sum" formatString="#,###.00" visible="false"/&gt;</div>
        <div style="padding-left:20px;">&lt;<a href="#XML_CalculatedMember">CalculatedMember</a> name="Margin" dimension="Measures" visible="false"&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Formula">Formula</a>&gt;([Measures].[Store Sales] - [Measures].[Store Cost]) / [Measures].[Store Cost]&lt;/<a href="#XML_Formula">Formula</a>&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_CalculatedMember">CalculatedMember</a>&gt;</div>
        <div style="padding-left:20px;">&lt;<a href="#XML_CalculatedMember">CalculatedMember</a> name="Store Sqft" dimension="Measures" visible="false"&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Formula">Formula</a>&gt;IIF([Store].CurrentMember.Level.Name = "Store Name", [Store].Properties("Store Sqft"), NULL)&lt;/<a href="#XML_Formula">Formula</a>&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_CalculatedMember">CalculatedMember</a>&gt;</div>
        <div style="padding-left:20px;">&lt;<a href="#XML_CalculatedMember">CalculatedMember</a> name="Margin per Sqft" dimension="Measures" visible="true"&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Formula">Formula</a>&gt;[Measures].[Margin] / [Measures].[Store Cost]&lt;/<a href="#XML_Formula">Formula</a>&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_CalculatedMemberProperty">CalculatedMemberProperty</a> name="FORMAT_STRING" value="$#,##0.00"/&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_CalculatedMember">CalculatedMember</a>&gt;</div>
    </code>
</blockquote>

<!--
########################
##  5.5 Named sets #####
######################## -->
<h1>5.5 Named sets<a name="Named_sets">&nbsp;</a></h1>

<p>The <code>WITH SET</code> clause of an MDX statement allows you to declare a set expression
which can be used throughout that query. For example,</p>

<blockquote>
  <code>WITH SET [Top Sellers] AS <br/>
&nbsp;&nbsp;&nbsp; 'TopCount([Warehouse].[Warehouse Name].MEMBERS, 5,
[Measures].[Warehouse Sales])'<br/>
SELECT <br/>
&nbsp;&nbsp;&nbsp; {[Measures].[Warehouse Sales]} ON COLUMNS,<br/>
&nbsp;&nbsp;&nbsp; {[Top Sellers]} ON ROWS<br/>
FROM [Warehouse]<br/>

WHERE [Time].[Year].[1997]</code>
</blockquote>

<p>The <code>WITH SET</code> clause is very similar to the <code>WITH MEMBER</code> clause,
and as you might expect, it has a construct in schema analogous to
<code>&lt;<a href="#XML_CalculatedMember">CalculatedMember</a>&gt;</code>. The
<code>&lt;<a href="#XML_NamedSet">NamedSet</a>&gt;</code> element allows you to define a
named set in your schema as part of a cube definition. It is implicitly available for
any query against that cube:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Cube">Cube</a> name="Warehouse"&gt;</div>
            <div style="padding-left:40px;">...</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_NamedSet">NamedSet</a> name="Top Sellers"&gt;</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_Formula">Formula</a>&gt;TopCount([Warehouse].[Warehouse Name].MEMBERS, 5, [Measures].[Warehouse Sales])&lt;/<a href="#XML_Formula">Formula</a>&gt;</div>
            <div style="padding-left:40px;">&lt;/<a href="#XML_NamedSet">NamedSet</a>&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_Cube">Cube</a>&gt;</div>
<br />
  SELECT <br/>
&nbsp;&nbsp;&nbsp; {[Measures].[Warehouse Sales]} ON COLUMNS,<br/>
&nbsp;&nbsp;&nbsp; {[Top Sellers]} ON ROWS<br/>
FROM [Warehouse]<br/>
WHERE [Time].[Year].[1997]</code>
</blockquote>

  <table id="table4" class="grayTable"  >
      <tr>
        <td>
        <b>Warehouse</b>
        </td>
        <td><b>Warehouse Sales</b></td>
      </tr>
      <tr>
        <td>Treehouse Distribution</td>
        <td align="right">31,116.37</td>
      </tr>
      <tr>
        <td>Jorge Garcia, Inc.</td>
        <td align="right">30,743.77</td>
      </tr>
      <tr>
        <td>Artesia Warehousing, Inc.</td>
        <td align="right">29,207.96</td>
      </tr>
      <tr>
        <td>Jorgensen Service Storage</td>
        <td align="right">22,869.79</td>
      </tr>
      <tr>
        <td>Destination, Inc.</td>
        <td align="right">22,187.42</td>
      </tr>
  </table>

<p>A named set defined against a cube is not inherited by a virtual cube defined against
that cube. (But you can define a named set against a virtual cube.)</p>

<p>You can also define a named set as global to a schema:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Cube">Schema</a>&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Cube">Cube</a> name="Sales" ... /&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Cube">Cube</a> name="Warehouse" ... /&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_VirtualCube">VirtualCube</a> name="Warehouse and Sales" .../&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_NamedSet">NamedSet</a> name="CA Cities" formula="{[Store].[USA].[CA].Children}"/&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_NamedSet">NamedSet</a> name="Top CA Cities"&gt;</div>
    			<div style="padding-left:60px">&lt;<a href="#XML_Formula">Formula</a>&gt;TopCount([CA Cities], 2, [Measures].[Unit Sales])&lt;/<a href="#XML_Formula">Formula</a>&gt;</div>
            <div style="padding-left:40px;">&lt;/<a href="#XML_NamedSet">NamedSet</a>&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_Cube">Schema</a>&gt;</div>
    </code>
</blockquote>

<p>A named set defined against a schema is available in all cubes and virtual cubes in that
schema. However, it is only valid if the cube contains dimensions with the names required to
make the formula valid. For example, it would be valid to use <code>[CA Cities]</code> in
queries against the <code>[Sales]</code> and <code>[Warehouse and Sales]</code> cubes, but
if you used it in a query against the <code>[Warehouse]</code> cube you would get an error,
because <code>[Warehouse]</code> does not have a <code>[Store]</code> dimension.</p>
<!--
########################
##  plugins        #####
######################## -->
<h3>6. Plug-ins<a name="Plugins">&nbsp;</a></h3>

<p>Sometimes Mondrian's schema language isn't flexible enough, or the MDX language isn't
powerful enough, to solve the problem at hand. What you want to do is add a little of your
own Java code into the Mondrian application, and a <dfn>plug-in</dfn> is a way to do this.</p>

<p>Each of Mondrian's extensions is technically a Service Provider Interface (SPI); in short,
a Java interface which you write code to implement, and which Mondrian will call at runtime.
You also need to register an extension (usually somewhere in your schema.xml file) and to
ensure that it appears on the classpath.</p>

<p>Plug-ins include <a href="#User-defined_function">
user-defined functions</a>; <a href="#Cell_formatter">
cell</a>, <a href="#Member_formatter">member</a> and
<a href="#Property_formatter">property formatters</a>;
<a href="#Schema_processor">dynamic schema processors</a> and
<a href="#DataSource_change_listener">data source change listeners</a>.
There is incomplete support for <a href="#Member_reader">member
readers</a> and <a href="#Cell_reader">cell readers</a>,
and in future we may support pluggable
<a href="api/mondrian/rolap/sql/SqlQuery.Dialect.html">
SQL dialects</a>.</p>

<p>Some plug-ins (user-defined function, member formatter, property
formatter, cell formatter) can be implemented in a scripting language
such as JavaScript. In this case, you do not need to write a Java
class; you just enclose the script code within
a <a href="#XML_Script">Script</a> element in the mondrian schema
file. Extensions implemented in scripting languages do not in general
perform as well as extensions implemented in Java, but they are much
more convenient because you do not need to compile any code. Just
modify the script code in the mondrian schema file and re-load the
schema. The shorter code-debug-fix cycle allows you to develop your
application much faster. Once you have implemented the plug-in in
script, if performance is still a concern, you can translate your
plug-in into Java.</p>

<p>Other extensions
include <a href="#Dynamic_datasource_xmla_servlet">Dynamic datasource
xmla servlet</a>.</p>

<!--
###################################
##  6.1 User-defined function #####
################################### -->
<h1>6.1 User-defined function<a name="User-defined_function">&nbsp;</a></h1>

<p>A user-defined function must have a public constructor and implement the
<a href="api/mondrian/spi/UserDefinedFunction.html">
mondrian.spi.UserDefinedFunction</a> interface. For example,</p>

<blockquote>
  <code>package com.example;<br/>
  <br/>
import mondrian.olap.*;<br/>

import mondrian.olap.type.*;<br/>
import mondrian.spi.UserDefinedFunction;<br/>
  <br/>
/**<br/>
&nbsp;* A simple user-defined function which adds one to its argument.<br/>
&nbsp;*/<br/>
public class PlusOneUdf implements UserDefinedFunction {<br/>
&nbsp;&nbsp;&nbsp; // public constructor<br/>

&nbsp;&nbsp;&nbsp; public PlusOneUdf() {<br/>
&nbsp;&nbsp;&nbsp; }<br/>
  <br/>
&nbsp;&nbsp;&nbsp; public String getName() {<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return "PlusOne";<br/>
&nbsp;&nbsp;&nbsp; }<br/>

  <br/>
&nbsp;&nbsp;&nbsp; public String getDescription() {<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return "Returns its argument
plus one";<br/>
&nbsp;&nbsp;&nbsp; }<br/>
  <br/>
&nbsp;&nbsp;&nbsp; public Syntax getSyntax() {<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return Syntax.Function;<br/>

&nbsp;&nbsp;&nbsp; }<br/>
  <br/>
&nbsp;&nbsp;&nbsp; public Type getReturnType(Type[] parameterTypes) {<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return new NumericType();<br/>
&nbsp;&nbsp;&nbsp; }<br/>
  <br/>
&nbsp;&nbsp;&nbsp; public Type[] getParameterTypes() {<br/>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return new Type[] {new
NumericType()};<br/>
&nbsp;&nbsp;&nbsp; }<br/>
  <br/>
&nbsp;&nbsp;&nbsp; public Object execute(Evaluator evaluator, Exp[]
arguments) {<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; final Object argValue =
arguments[0].evaluateScalar(evaluator);<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if (argValue instanceof
Number) {<br/>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
return new Double(((Number) argValue).doubleValue() + 1);<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } else {<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; //
Argument might be a RuntimeException indicating that<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; //
the cache does not yet have the required cell value. The<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; //
function will be called again when the cache is loaded.<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
return null;<br/>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br/>
&nbsp;&nbsp;&nbsp; }<br/>
  <br/>
&nbsp;&nbsp;&nbsp; public String[] getReservedWords() {<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return null;<br/>
&nbsp;&nbsp;&nbsp; }<br/>

}</code>
</blockquote>

<p>Declare it in your schema:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Schema">Schema</a>&gt;</div>
            <div style="padding-left:40px;">...</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_UserDefinedFunction">UserDefinedFunction</a> name="PlusOne" className="com.example.PlusOneUdf"/&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_Schema">Schema</a>&gt;</div>
    </code>
</blockquote>

<p>And use it in any MDX statement:</p>

<blockquote>
  <code>WITH MEMBER [Measures].[Unit Sales Plus One] <br/>
&nbsp;&nbsp;&nbsp; AS 'PlusOne([Measures].[Unit
Sales])'<br/>
SELECT<br/>
&nbsp;&nbsp;&nbsp; {[Measures].[Unit Sales]} ON COLUMNS,<br/>

&nbsp;&nbsp;&nbsp; {[Gender].MEMBERS} ON ROWS<br/>
FROM [Sales]</code>
</blockquote>

<p>If a user-defined function has a public constructor with one string argument, Mondrian
will pass in the function's name. Why? This allows you to define two or more user-defined
functions using the same class:</p>

<blockquote>
  <code>package com.example;<br/>
  <br/>
import mondrian.olap.*;<br/>
import mondrian.olap.type.*;<br/>

import mondrian.spi.UserDefinedFunction;<br/>
  <br/>
/**<br/>
&nbsp;* A user-defined function which either adds one to or <br/>
&nbsp;* subtracts one from its argument.<br/>
&nbsp;*/<br/>
public class PlusOrMinusOneUdf implements UserDefinedFunction {<br/>
  &nbsp;&nbsp;&nbsp; private final name;<br/>

&nbsp;&nbsp;&nbsp; private final isPlus;<br/>
  <br/>
  &nbsp;&nbsp;&nbsp; // public constructor with one argument<br/>
&nbsp;&nbsp;&nbsp; public PlusOneUdf(String
name) {<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; this.name = name;<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if (name.equals("PlusOne")) {<br/>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
isPlus = true;<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } else if
(name.equals("MinusOne")) {<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
isPlus = false;<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } else {<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
throw new IllegalArgumentException("Unexpected name " + name);<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br/>

&nbsp;&nbsp;&nbsp; }<br/>
  <br/>
&nbsp;&nbsp;&nbsp; public String getName() {<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return name;<br/>
&nbsp;&nbsp;&nbsp; }<br/>
  <br/>
&nbsp;&nbsp;&nbsp; public String getDescription() {<br/>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return "Returns its argument
plus or minus one";<br/>
&nbsp;&nbsp;&nbsp; }<br/>
  <br/>
&nbsp;&nbsp;&nbsp; public Syntax getSyntax() {<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return Syntax.Function;<br/>
&nbsp;&nbsp;&nbsp; }<br/>

  <br/>
&nbsp;&nbsp;&nbsp; public Type getReturnType(Type[] parameterTypes) {<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return new NumericType();<br/>
&nbsp;&nbsp;&nbsp; }<br/>
  <br/>
&nbsp;&nbsp;&nbsp; public Type[] getParameterTypes() {<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return new Type[] {new
NumericType()};<br/>

&nbsp;&nbsp;&nbsp; }<br/>
  <br/>
&nbsp;&nbsp;&nbsp; public Object execute(Evaluator evaluator, Exp[]
arguments) {<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; final Object argValue =
arguments[0].evaluateScalar(evaluator);<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if (argValue instanceof
Number) {<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if (isPlus) {<br/>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
return new Double(((Number) argValue).doubleValue() + 1);<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }
else {<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
return new Double(((Number) argValue).doubleValue() - 1);<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } else {<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; //
Argument might be a RuntimeException indicating that<br/>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; //
the cache does not yet have the required cell value. The<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; //
function will be called again when the cache is loaded.<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
return null;<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br/>
&nbsp;&nbsp;&nbsp; }<br/>
  <br/>

&nbsp;&nbsp;&nbsp; public String[] getReservedWords() {<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return null;<br/>
&nbsp;&nbsp;&nbsp; }<br/>
}</code>
</blockquote>

<p>and register two the functions in your schema:</p>

<blockquote>
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Schema">Schema</a>&gt;</div>
            <div style="padding-left:40px;">...</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_UserDefinedFunction">UserDefinedFunction</a> name="PlusOne" className="com.example.PlusOrMinusOneUdf"&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_UserDefinedFunction">UserDefinedFunction</a> name="MinusOne" className="com.example.PlusOrMinusOneUdf"&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_Schema">Schema</a>&gt;</div>
    </code>
</blockquote>

<p>If you're tired of writing duplicate user-defined function
declarations in schema files, you can pack your user-defined function
implemention classes into a jar file with a embedded
<code>META-INF/services/mondrian.spi.UserDefinedFunction</code>
resource file. This resource file contains class names of
implementations of interface mondrian.spi.UserDefinedFunction, one
name per line.  For more details, you may look into
<code>src/main/META-INF/services/mondrian.spi.UserDefinedFunction</code>
in the source distribution and the
<a href="http://java.sun.com/j2se/1.5.0/docs/guide/jar/jar.html#Service%20Provider">
Service Provider</a> section of the specification of JAR files.
User-defined functions declared by this means are available to all
mondrian schemas in the JVM.</p>

<p>Caution: you can't define more than one user-defined function
implementations in one class when you declare user-defined functions
in this way. One function will be loaded for each class, and given the
name that the <code>getName()</code> method returns.</p>

<p>User-defined functions can also be implemented in a scripting
language, such as JavaScript. These functions may not perform quite as
well as Java UDFs or built-in functions, but they are a lot more
convenient to implement.</p>

<p>To define a UDF in script, use the <a href="#XML_Script">Script</a> element and include within it the followimg functions:</p>
<ul>
<li><code>getName()</code></li>
<li><code>getDescription()</code></li>
<li><code>getSyntax()</code></li>
<li><code>getParameterTypes()</code></li>
<li><code>getReturnType(parameterTypes)</code></li>
<li><code>execute(evaluator, arguments)</code></li>
</ul>

<p>The getName(), getDescription(), getReservedWords() and getSyntax()
methods are optional; getName() defaults to the name attribute in the
UserDefinedFunction element, getDescription() defaults to the name,
getReservedWords() returns the empty list, and getSyntax() defaults
to <a href="api/mondrian/olap/Syntax.html#Function">mondrian.olap.Syntax.Function</a>. The
other methods have similar meanings to the ones in the
UserDefinedFunction SPI.</p>

<p>Here is an example of the factorial function as a JavaScript UDF:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_UserDefinedFunction">UserDefinedFunction</a> name="Factorial"&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Script">Script</a> language="JavaScript"&gt;</div>
                <div style="padding-left:60px">function getParameterTypes() {</div>
                <div style="padding-left:80px">  return new Array(new mondrian.olap.type.NumericType());</div>
                <div style="padding-left:60px">}</div>
                <div style="padding-left:60px">function getReturnType(parameterTypes) {</div>
                <div style="padding-left:80px">  return new mondrian.olap.type.NumericType();</div>
                <div style="padding-left:60px">}</div>
                <div style="padding-left:60px">function execute(evaluator, arguments) {</div>
                <div style="padding-left:80px">  var n = arguments[0].evaluateScalar(evaluator);</div>
                <div style="padding-left:80px">  return factorial(n);</div>
                <div style="padding-left:60px">}</div>
                <div style="padding-left:60px">function factorial(n) {</div>
                <div style="padding-left:80px">  return n &lt;= 1 ? 1 : n * factorial(n - 1);</div>
                <div style="padding-left:60px">}</div>
            <div style="padding-left:40px;">&lt;/<a href="#XML_Script">Script</a>&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_UserDefinedFunction">UserDefinedFunction</a>&gt;</div>
    </code>
</blockquote>



<!--
########################
##  Member reader  #####
######################## -->
<h1>6.2 Member reader<a name="Member_reader">&nbsp;</a></h1>

<p>A <dfn>member reader</dfn> is a means of accessing members. Hierarchies are usually based
upon a dimension table (an 'arm' of a star schema), and are therefore populated using SQL.
But even if your data doesn't reside in an RDBMS, you can make it appear as a hierarchy
by writing a Java class called a <dfn>custom member reader</dfn>.</p>

<p>Here are a couple of examples:</p>

<ol>
  <li><code>DateSource</code> (to be written) generates a time
hierarchy. Conventionally, data warehouse implementors generate a table
containing a row for every date their system is ever likely to deal
with. But the problem is that this table needs to be loaded, and as
time goes by, they will have to remember to add more rows. DateSource
generates date members in memory, and on demand.</li>

  <li><code>FileSystemSource</code> (to be written) presents the file
system as a hierarchy of directories and files. Since a directory can
have a parent which is itself a directory, it is a parent-child
hierarchy. Like the time hierarchy created by DateSource, this is a
virtual hierarchy: the member for a particular file is only created
when, and if, that file's parent directory is expanded.</li>
  <li><code>ExpressionMemberReader</code> (to be written) creates a
hierarchy based upon an expression.</li>
</ol>

<p>A custom member reader must implement the
<a href="api/mondrian/rolap/MemberSource.html">
mondrian.rolap.MemberSource</a> interface. If you need to implement a larger set of member
operations for fine-grained control, implement the derived
<a href="api/mondrian/rolap/MemberReader.html">
mondrian.rolap.MemberReader</a> interface; otherwise, Mondrian wrap your reader in a
<a href="api/mondrian/rolap/MemberSource.html">
mondrian.rolap.CacheMemberReader</a> object. Your member reader must have a public constructor
which takes <code>(<a href="api/mondrian/rolap/RolapHierarchy.html">
RolapHierarchy</a>, <a href="http://java.sun.com/j2se/1.4/docs/api/java/util/Properties.html">
Properties</a>)</code> parameters, and throws no checked exceptions.</p>

<p>Member readers are declared using the <code><a href="#XML_Hierarchy">&lt;Hierarchy&gt;</a>
</code> element's <code>memberReaderClass</code> attribute; any <code><a href="#XML_Parameter">
&lt;Parameter&gt;</a></code> child elements are passed via the <code>properties</code>
constructor parameter. Here is an example:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Dimension">Dimension</a> name="Has bought dairy"&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Hierarchy">Hierarchy</a> hasAll="true" memberReaderClass="mondrian.rolap.HasBoughtDairySource"&gt;</div>
			    <div style="padding-left:60px">&lt;<a href="#XML_Level">Level</a> name="Has bought dairy" uniqueMembers="true"/&gt;</div>
			    <div style="padding-left:60px">&lt;<a href="#XML_Parameter">Parameter</a> name="expression" value="not used"/&gt;</div>
            <div style="padding-left:40px;">&lt;/<a href="#XML_Hierarchy">Hierarchy</a>&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_Dimension">Dimension</a>&gt;</div>
    </code>
</blockquote>

<!--
#########################
##  6.3 Cell reader #####
######################### -->
<h1>6.3 Cell reader<a name="Cell_reader">&nbsp;</a></h1>

<p>Not implemented yet. Syntax would be something like</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Measure">Measure</a> name="name" cellReaderClass=&quot;com.example.MyCellReader&quot;/&gt;</div>
    </code>
</blockquote>

<p>and the class "com.example.MyCellReader" would have to implement the <code>
<a href="api/mondrian/olap/CellReader.html">
mondrian.olap.CellReader</a></code> interface.</p>

<!--
############################
##  6.4 Cell formatter #####
############################ -->
<h1>6.4 Cell formatter<a name="Cell_formatter">&nbsp;</a></h1>

<p>A cell formatter modifies the behavior of <code>
<a href="api/mondrian/olap/Cell.html#getFormattedValue%28%29">
Cell.getFormattedValue()</a></code>. The class must implement the <code>
<a href="api/mondrian/spi/CellFormatter.html">
mondrian.spi.CellFormatter</a></code> interface, and is specified like this:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Measure">Measure</a> name="name"&gt;</div>
            <div style="padding-left:40px">&lt;<a href="#XML_CellFormatter">CellFormatter</a> className=&quot;com.example.MyCellFormatter&quot;/&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_Measure">Measure</a>&gt;</div>
    </code>
</blockquote>

<p>(The previous syntax, using the 'formatter' attribute of the Measure
element, is deprecated, and will be removed in mondrian-4.0.)</p>

<p>You can specify a formatter in a scripting language such as
JavaScript, using the <a href="#XML_Script">Script</a> element:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Measure">Measure</a> name="name"&gt;</div>
            <div style="padding-left:40px">&lt;<a href="#XML_CellFormatter">CellFormatter</a>&gt;</div>
            <div style="padding-left:60px">&lt;<a href="#XML_Script">Script</a> language=&quot;JavaScript&quot;&gt;</div>
                <div style="padding-left:60px">&lt;/<a href="#XML_Script">Script</a>&gt;</div>
            <div style="padding-left:40px">&lt;/<a href="#XML_CellFormatter">CellFormatter</a>&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_Measure">Measure</a>&gt;</div>
    </code>
</blockquote>

<p>The script has available a <code>value</code> variable,
corresponding to the parameter of
the <a href="api/mondrian/spi/CellFormatter.html#formatCell(Object)">
mondrian.spi.CellFormatter.formatCell(Object value)</a></code>
method. The code fragment can have multiple statements, but must end
in a <code>return</code> statement.</p>

<p>For a calculated member that belongs to a cube or virtual cube, you
can also use the <a href="#XML_CellFormatter">CellFormatter</a>
element:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_CalculatedMember">CalculatedMember</a> name=&quot;name&quot; dimension=&quot;dimension&quot;&gt;</div>
            <div style="padding-left:40px">&lt;<a href="#XML_Formula">Formula</a>&gt;</div>
                <div style="padding-left:60px">[Measures].[Unit Sales] * 2</div>
            <div style="padding-left:40px">&lt;/<a href="#XML_Formula">Formula</a>&gt;</div>
            <div style="padding-left:40px">&lt;<a href="#XML_CellFormatter">CellFormatter</a>&gt;</div>
                <div style="padding-left:60px">&lt;<a href="#XML_Script">Script</a> language=&quot;JavaScript&quot;&gt;</div>
                    <div style="padding-left:80px">var s = value.toString();</div>
                    <div style="padding-left:80px">while (s.length() < 20) {</div>
                    <div style="padding-left:100px">s = &quot;0&quot; + s;</div>
                    <div style="padding-left:80px">}</div>
                    <div style="padding-left:80px">return s;</div>
                <div style="padding-left:60px">&lt;/<a href="#XML_Script">Script</a>&gt;</div>
            <div style="padding-left:40px">&lt;/<a href="#XML_CellFormatter">CellFormatter</a>&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_Measure">Measure</a>&gt;</div>
    </code>
</blockquote>


<p>You can also define a formatter by setting the <code>CELL_FORMATTER</code> property of the member to the name of the formatter class.</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_CalculatedMember">CalculatedMember</a> name="name" formatter="com.example.MyCellFormatter"&gt;</div>
            <div style="padding-left:40px">&lt;<a href="#XML_CalculatedMemberProperty">CalculatedMemberProperty</a> name=&quot;CELL_FORMATTER&quot; value=&quot;com.example.MyCellFormatter&quot/&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_CalculatedMember">CalculatedMember</a>&gt;</div>
    </code>
</blockquote>

<p>For a calculated measure defined in the <code>WITH MEMBER</code> clause of an
MDX query, you can set the same property in the MDX to achieve the same effect:</p>

<blockquote>
  <code>WITH MEMBER [Measures].[Foo]<br/>
&nbsp; AS '[Measures].[Unit Sales] * 2',<br/>
&nbsp;&nbsp; CELL_FORMATTER='com.example.MyCellFormatter'<br/>
	SELECT {[Measures].[Unit Sales], [Measures].[Foo]} ON COLUMNS,<br/>
&nbsp;&nbsp;&nbsp; {[Store].Children} ON ROWS<br/>
	FROM [Sales]</code></blockquote>

<p>To define a scripted formatter, use
the <code>CELL_FORMATTER_SCRIPT</code>
and <code>CELL_FORMATTER_SCRIPT_LANGUAGE</code> properties:</p>

<blockquote>
  <code>WITH MEMBER [Measures].[Foo]<br/>
&nbsp; AS '[Measures].[Unit Sales] * 2',<br/>
&nbsp;&nbsp; CELL_FORMATTER_SCRIPT_LANGUAGE='JavaScript',<br/>
&nbsp;&nbsp; CELL_FORMATTER_SCRIPT='var s = value.toString(); while (s.length() < 20) s = &quot;0&quot; + s; return s;'<br/>
	SELECT {[Measures].[Unit Sales], [Measures].[Foo]} ON COLUMNS,<br/>
&nbsp;&nbsp;&nbsp; {[Store].Children} ON ROWS<br/>
	FROM [Sales]</code></blockquote>


<p>The cell formatter property is ignored if a member does not belong to the
<code>[Measures]</code> dimension.</p>

<!--
##############################
##  6.5 Member formatter #####
############################## -->
<h1>6.5 Member formatter<a name="Member_formatter">&nbsp;</a></h1>

<p>A member formatter modifies the behavior of <code>
<a href="api/mondrian/olap/Member.html#getCaption%28%29">
Member.getCaption()</a></code>. The class must implement
the <code><a href="api/mondrian/spi/MemberFormatter.html">
mondrian.spi.MemberFormatter</a></code> interface, and is specified
like this:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Level">Level</a> name="name" column=&quot;column&quot;&gt;</div>
            <div style="padding-left:40px">&lt;<a href="#XML_MemberFormatter">MemberFormatter</a> className=&quot;com.example.MyMemberFormatter&quot;/&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_Level">Level</a>&gt;</div>
    </code>
</blockquote>

<p>(The previous syntax, using the 'formatter' attribute of the Level
element, is deprecated, and will be removed in mondrian-4.0.)</p>

<p>You can specify a formatter in a scripting language such as
JavaScript, using the <a href="#XML_Script">Script</a> element:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Level">Level</a> name="name" column=&quot;column&quot;&gt;</div>
            <div style="padding-left:40px">&lt;<a href="#XML_MemberFormatter">MemberFormatter</a>&gt;</div>
                <div style="padding-left:60px">&lt;<a href="#XML_Script">Script</a> language=&quot;JavaScript&quot;&gt;</div>
                    <div style="padding-left:80px">return member.getName().toUpperCase();</div>
                <div style="padding-left:60px">&lt;/<a href="#XML_Script">Script</a>&gt;</div>
            <div style="padding-left:40px">&lt;/<a href="#XML_MemberFormatter">MemberFormatter</a>&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_Level">Level</a>&gt;</div>
    </code>
</blockquote>

<p>The script has available a <code>member</code> variable,
corresponding to the parameter of
the <a href="api/mondrian/spi/MemberFormatter.html#formatMember(Member)">
mondrian.spi.MemberFormatter.formatMember(Member member)</a></code>
method. The code fragment can have multiple statements, but must end
in a <code>return</code> statement.</p>


<!--
################################
##  6.6 Property formatter #####
################################ -->
<h1>6.6 Property formatter<a name="Property_formatter">&nbsp;</a></h1>

<p>A property formatter modifies the behavior of <code>
<a href="api/mondrian/olap/Member.html#getPropertyFormattedValue%28java.lang.String%29">
Property.getPropertyFormattedValue()</a></code>. The class must implement the <code>
<a href="api/mondrian/spi/PropertyFormatter.html">
mondrian.spi.PropertyFormatter</a></code> interface, and is specified like this:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Level">Level</a> name="MyLevel" column="LevelColumn" uniqueMembers="true"&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Property">Property</a> name="MyProp" column="PropColumn"&gt;</div>
                <div style="padding-left:60px">&lt;<a href="#XML_PropertyFormatter">PropertyFormatter</a> className=&quot;com.example.MyPropertyFormatter&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;/<a href="#XML_Property">Property</a></div>
        <div style="padding-left:20px;">&lt;<a href="#XML_Level">Level</a>/&gt;</div>
    </code>
</blockquote>

<p>(The previous syntax, using the 'formatter' attribute of the Property
element, is deprecated, and will be removed in mondrian-4.0.)</p>

<p>You can specify a formatter in a scripting language such as
JavaScript, using the <a href="#XML_Script">Script</a> element:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Level">Level</a> name="name" column=&quot;column&quot;&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Property">Property</a> name="MyProp" column="PropColumn"&gt;</div>
                <div style="padding-left:60px">&lt;<a href="#XML_PropertyFormatter">PropertyFormatter</a>&gt;</div>
                <div style="padding-left:80px">&lt;<a href="#XML_Script">Script</a> language=&quot;JavaScript&quot;&gt;</div>
                    <div style="padding-left:100px">return member.getName().toUpperCase();</div>
                <div style="padding-left:80px">&lt;/<a href="#XML_Script">Script</a>&gt;</div>
                <div style="padding-left:60px">&lt;/<a href="#XML_PropertyFormatter">PropertyFormatter</a>&gt;</div>
            <div style="padding-left:40px;">&lt;/<a href="#XML_Property">Property</a>&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_Level">Level</a>&gt;</div>
    </code>
</blockquote>

<p>The script has
available <code>member</code>, <code>propertyName</code>
and <code>propertyValue</code> variables, corresponding to the
parameters of
the <a href="api/mondrian/spi/PropertyFormatter.html#formatProperty(Member,
String, Object)"> mondrian.spi.PropertyFormatter.formatProperty(Member
member, String propertyName, Object propertyValue)</a></code> method.
The code fragment can have multiple statements, but must end in
a <code>return</code> statement.</p>

<!--
##############################
##  6.7 Schema processor #####
############################## -->
<h1>6.7 Schema processor<a name="Schema_processor">&nbsp;</a></h1>

<p>A schema processor implements the <code>
<a href="api/mondrian/spi/DynamicSchemaProcessor.html">
    mondrian.spi.DynamicSchemaProcessor</a></code> interface. It is specified as part of
the connection string, like this:</p>

<blockquote>
  <code>Jdbc=jdbc:odbc:MondrianFoodMart; JdbcUser=ziggy;
JdbcPassword=stardust; DynamicSchemaProcessor=com.example.MySchemaProcessor</code>
</blockquote>

<p>The effect is that when reading the contents of the schema from a URL, Mondrian turns to
the schema processor rather than Java's default URL handler. This gives the schema reader
the opportunity to run a schema through a filter, or even generate an entire schema on the fly.</p>

<p>When <code>DynamicSchemaProcessor</code> is specified, schema would
be processed and reloaded on every ROLAP connection request. Property
<code>UseContentChecksum</code> should be used along with a schema processor
to enable caching of the schema:</p>

<blockquote><code>DataSource=java:/jdbc/MyWarehouse;
DynamicSchemaProcessor=com.example.MySchemaProcessor;
UseContentChecksum=true</code></blockquote>

<p>In this case once loaded schema would be cached until it's change. If
schema content has changed, it would be reloaded (and processed).</p>

<p>Dynamic schemas are a very powerful construct. As we shall see, an important application
for them is <a href="#I18n">internationalization</a>.</p>

<!--
#########################################
##  6.8 Data source change listener #####
######################################### -->
<h1>6.8 Data source change listener<a name="DataSource_change_listener">&nbsp;</a></h1>

<p>A data source change listener implements the <code>
<a href="api/mondrian/spi/DataSourceChangeListener.html">
mondrian.spi.DataSourceChangeListener</a></code> interface. It is specified as part of
the connection string, like this:</p>

<blockquote>
  <code>Jdbc=jdbc:odbc:MondrianFoodMart; JdbcUser=ziggy;
JdbcPassword=stardust; DataSourceChangeListener=com.example.MyChangeListener;</code>
</blockquote>

<p>Everytime mondrian has to decide whether it will use data from cache, it
will call the change listener.  When the change listener tells mondrian
the datasource has changed for a dimension, cube, ... then mondrian will
flush the cache and read from database again.</p>

<p>This class should be called in mondrian before any data is read, so
even before cache is build.  This way, the plugin is able to register
the first timestamp mondrian tries to read the datasource.</p>

<p>Each time a query is started, aggregate cache is checked to see if
it has changed.  If so, cache will be flushed and aggregates will be
reloaded from the data source.</p>

<p>Here is an example of a data source change listener plugin class:

<blockquote>
  <code>&nbsp;package com.example;<br/>
  <br/>
&nbsp;//...<br/>
&nbsp;import javax.sql.DataSource;<br/>
&nbsp;import java.sql.Connection;<br/>
&nbsp;import java.sql.ResultSet;<br/>
&nbsp;import java.sql.SQLException;<br/>
&nbsp;import java.sql.Timestamp;<br/>
&nbsp;//...<br/>
&nbsp;import mondrian.olap.MondrianDef;<br/>
&nbsp;import mondrian.rolap.RolapHierarchy;<br/>
&nbsp;import mondrian.rolap.RolapUtil;<br/>
&nbsp;import mondrian.rolap.agg.Aggregation;<br/>
&nbsp;import mondrian.rolap.RolapStar;<br/>
&nbsp;import mondrian.spi.impl.DataSourceChangeListenerImpl;<br/>
&nbsp;//...<br/>
<br/>
&nbsp;public class MyChangeListener extends DataSourceChangeListenerImpl {<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;public MyChangeListener() {<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;public synchronized boolean isHierarchyChanged(RolapHierarchy hierarchy) {<br/><br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;// Since this function is called many times, it is a good idea to not check the database every time<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;// And use some sort of time interval...<br/><br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;// Get name of the table (does not work if based on view)<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;String tableName = getTableName(hierarchy);<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Connection jdbcConnection = null;<br/>
&nbsp;<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DataSource dataSource =<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;hierarchy.getRolapSchema().getInternalConnection().getDataSource();<br/>
&nbsp;<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;try {<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;jdbcConnection = dataSource.getConnection();<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if (jdbcConnection != null) {<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;// Check database whether hierarchy data source has changed<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;// ...<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;public synchronized boolean isAggregationChanged(Aggregation aggregation) {<br/><br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;// The first time, register star and bitKey and remember first time of access...<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;RolapStar star = aggregation.getStar();<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;BitKey bitKey = aggregation.getConstrainedColumnsBitKey();<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;// The first time this function is called, only the bitKey is set,<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;// the columns are not filled up yet.<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;RolapStar.Column[] columns = aggregation.getColumns();<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if (columns != null) {<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;// Check database...<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}<br/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;}<br/>
&nbsp;}<br/>
</code>
</blockquote>
<!--
#############################################
##  6.9 Dynamic datasource xmla servlet #####
############################################# -->
<h1>6.9 Dynamic datasource xmla servlet<a name="Dynamic_datasource_xmla_servlet">&nbsp;</a></h1>

<p>The <code>DynamicDatasourceXmlaServlet</code> extends <code>DefaultXmlaServlet</code>, adding the ability to dynamically load 
the <code>datasources.xml</code> file. For every client request that it receives, it checks
for updates to the content of <code>datasources.xml</code>. It selectively 
clears cache for catalogs that have changed or no longer exist in <code>datasources.xml</code>. 
The servlet considers a
catalog as changed when either of its properties (DataSourceInfo, definition
properties on <a href="api/mondrian/xmla/DataSourcesConfig.Catalog.html">
DataSourcesConfig.Catalog</a>) are different. It identifies catalog by name.</p>

<p>This servlet complements the dynamic catalog loading capability based on
<a href="configuration.html#Connect_string_properties">UseContentChecksum</a>.
It does not check the catalog content for updates. There is no overlap in the
functionality. Both together will give full dynamic datasource and
catalog configuration capability.</p>

<p>To use DynamicDatasourceXmlaServlet, change definition of the <code>MondrianXmlaServlet</code> 
servlet in <code>web.xml</code>:</p>

<blockquote>
  <code>
    &lt;servlet&gt;<br/>
    &nbsp;&nbsp;&nbsp;&lt;servlet-name&gt;MondrianXmlaServlet&lt;/servlet-name&gt;<br/>
    &nbsp;&nbsp;&nbsp;&lt;servlet-class&gt;mondrian.xmla.impl.<b>DynamicDatasourceXmlaServlet</b>&lt;/servlet-class&gt;<br/>
    &nbsp;&nbsp;&nbsp; ...<br/>
    &lt;/servlet&gt;</code></blockquote>

<p>This implementation has a limitation. It requires catalog name to be unique
across all datasources and may not work correctly otherwise.</p>

<!--
#################################
##  7. Internationalization #####
################################# -->
<h3>7. Internationalization<a name="I18n">&nbsp;</a></h3>

<p>An internationalized Mondrian application would have a schema for each language, where the
caption of each object appears in the local language. For example, the <code>[Product]</code>
dimension would have the caption "Product" in English and "Produit" in French.</p>

<p>It is unwise to translate the actual names of the schema objects, because then the MDX
statements would need to be changed also. All that you need to change is the caption. Every
schema object (schema, cube, virtual cube, dimension, hierarchy, level, measure, 
named set) has a caption attribute, and user
interfaces such as JPivot and Pentaho Analyzer display the caption rather than the real name. Additionally:</p>

<ul>
  <li>Every schema object has a description attribute.</li>
	<li>A hierarchy can have an <code>allMemberCaption</code> attribute
as display value of the "All" member. </li>
  <li>For the schema we can set a display value of the "measures"
dimension by the <code>measuresCaption</code> attribute. </li>
	<li>A calculated member has properties CAPTION and DESCRIPTION that appear 
	as caption and description if the member is a measure (that is, a member of 
	the <code>Measures</code> dimension).</li>
</ul>

<p>One way to create an internationalized application is to create a
copy of the schema file for each language, but these are difficult to
maintain. A better way is to use the <a
 href="api/mondrian/i18n/LocalizingDynamicSchemaProcessor.html">
LocalizingDynamicSchemaProcessor</a> class to perform dynamic
substitution on a single schema file.</p>
<!--
#########################################
##  7.1 Localizing schema processor #####
######################################### -->
<h1>7.1 Localizing schema processor</h1>

<p>First, write your schema using variables as values for <code>caption</code>,
<code>description</code>,
<code>allMemberCaption</code> and <code>measuresCaption</code> attributes as follows:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Schema">Schema</a> measuresCaption="%{foodmart.measures.caption}"&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Dimension">Dimension</a> name="Store" caption="%{foodmart.dimension.store.caption}" 
				description=&quot;%{foodmart.dimension.store.description}&quot;&gt;</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_Hierarchy">Hierarchy</a> hasAll="true" allMemberName="All Stores" allMemberCaption="%{foodmart.dimension.store.allmember.caption =All Stores}" primaryKey="store_id"&nbsp; 
					caption=&quot;%{foodmart.hierarchy.store.country.caption}&quot; 
					description=&quot;%{foodmart.hierararchy.store.country.description}&gt;</div>
                    <div style="padding-left:80px">&lt;<a href="#XML_Table">Table</a> name="store"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_Level">Level</a> name="Store Country" column="store_country" uniqueMembers="true" caption="%{foodmart.dimension.store.country.caption}" 
						description=&quot;%{foodmart.dimension.store.country.description}&quot;/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_Level">Level</a> name="Store State" column="store_state" uniqueMembers="true" caption="%{foodmart.dimension.store.state.caption}" 
						description=&quot;%{foodmart.dimension.store.state.description}&quot;/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_Level">Level</a> name="Store City" column="store_city" uniqueMembers="false" caption="%{foodmart.dimension.store.city.caption}" 
						description=&quot;%{foodmart.dimension.store.city.description}&quot;/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_Level">Level</a> name="Store Name" column="store_name" uniqueMembers="true" caption="%{foodmart.dimension.store.name.caption}" 
						description=&quot;%{foodmart.dimension.store.name.description}&quot;&gt;</div>
                        <div style="padding-left:100px">&lt;<a href="#XML_Property">Property</a> name="Store Type" column="store_type" caption="%{foodmart.dimension.store. name.property_type.caption}" 
							description=&quot;%{foodmart.dimension.store. name.property_type.description}&quot;/&gt;</div>
        				<div style="padding-left:100px">&lt;<a href="#XML_Property">Property</a> name="Store Manager" column="store_manager" caption="%{foodmart.dimension.store. name.property_manager.caption}" 
							description=&quot;%{foodmart.dimension.store. name.property_manager.description}&quot;/&gt;</div>
                        <div style="padding-left:100px">&lt;<a href="#XML_Property">Property</a> name="Store Sqft" column="store_sqft" type="Numeric" caption="%{foodmart.dimension.store. name.property_storesqft.caption}" 
							description=&quot;%{foodmart.dimension.store. 
							name.property_storesqft.description}&quot;/&gt;</div>
        				<div style="padding-left:100px">&lt;<a href="#XML_Property">Property</a> name="Grocery Sqft" column="grocery_sqft" type="Numeric"/&gt;</div>
				        <div style="padding-left:100px">&lt;<a href="#XML_Property">Property</a> name="Frozen Sqft" column="frozen_sqft" type="Numeric"/&gt;</div>
				        <div style="padding-left:100px">&lt;<a href="#XML_Property">Property</a> name="Meat Sqft" column="meat_sqft" type="Numeric"/&gt;</div>
        				<div style="padding-left:100px">&lt;<a href="#XML_Property">Property</a> name="Has coffee bar" column="coffee_bar" type="Boolean"/&gt;</div>
				        <div style="padding-left:100px">&lt;<a href="#XML_Property">Property</a> name="Street address" column="store_street_address" type="String"/&gt;</div>
                    <div style="padding-left:80px;">&lt;/<a href="#XML_Level">Level</a>&gt;</div>
    			<div style="padding-left:60px">&lt;/<a href="#XML_Hierarchy">Hierarchy</a>&gt;</div>
            <div style="padding-left:40px;">&lt;/<a href="#XML_Dimension">Dimension</a>&gt;</div>
<br/>
            <div style="padding-left:40px;">&lt;<a href="#XML_Cube">Cube</a> name="Sales" caption="%{foodmart.cube.sales.caption}" 
				description=&quot;%{foodmart.cube.sales.description}&quot;&gt;</div>
                <div style="padding-left:60px;">...</div>
    			<div style="padding-left:60px">&lt;<a href="#XML_DimensionUsage">DimensionUsage</a> name="Store" source="Store" foreignKey="store_id"&nbsp; 
					caption=&quot;%{foodmart.cube.sales.name.caption}&quot; 
					description=&quot;%{foodmart.cube.sales.name.description}&quot;/&gt;</div>
                <div style="padding-left:60px;">...</div>
                <div style="padding-left:60px">&lt;<a href="#XML_Measure">Measure</a> name="Unit Sales" column="unit_sales" caption="%{foodmart.cube.sales.measure.unitsales.caption}" 
					description=&quot;%{foodmart.cube.sales.measure.unitsales.description}&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;/<a href="#XML_Cube">Cube</a>&gt;</div>
        <div style="padding-left:10px;">&lt;/<a href="#XML_Schema">Schema</a>&gt;</div>
    </code>
</blockquote>

<p>As usual, the default caption for any cube, measure, dimension or
level without a <code>caption</code> attribute is the name of the
element. A hierarchy's default caption is the caption of its dimension;
for example, the <code>[Store]</code> hierarchy has no <code>caption</code>
defined, so it inherits the <code>caption</code> attribute from its
parent, the <code>
[Store]</code> dimension.</p>
<p>Next, add the dynamic schema processor and locale to your connect
string. For example,</p>

<blockquote>
  <code>Provider=mondrian; <i><b>Locale=en_US;
DynamicSchemaProcessor=&shy;mondrian.i18n.LocalizingDynamicSchemaProcessor;</b>
  </i>Jdbc=&shy;jdbc:odbc:MondrianFoodMart; Catalog=&shy;/WEB-INF/FoodMart.xml</code>
</blockquote>

<p>Now, for each locale you wish to support, provide a resource file
named <code>
locale_<i>{locale}</i>.properties</code>. For example,</p>

<blockquote>
  <code># locale.properties: Default resources<br/>
foodmart.measures.caption=Measures<br/>
foodmart.dimension.store.country.caption=Store Country<br/>

foodmart.dimension.store.name.property_type.column= store_type<br/>
foodmart.dimension.store.country.member.caption= store_country<br/>
foodmart.dimension.store.name.property_type.caption =Store Type<br/>
foodmart.dimension.store.name.caption =Store Name<br/>
foodmart.dimension.store.state.caption =Store State<br/>
foodmart.dimension.store.name.property_manager.caption =Store Manager<br/>
foodmart.dimension.store.name.property_storesqft.caption =Store Sq. Ft.<br/>
foodmart.dimension.store.allmember.caption =All Stores<br/>
foodmart.dimension.store.caption =Store<br/>

foodmart.cube.sales.caption =Sales<br/>
foodmart.dimension.store.city.caption =Store City<br/>
foodmart.cube.sales.measure.unitsales =Unit Sales</code>
</blockquote>

<p>and</p>

<blockquote>
  <code># locale_hu.properties: Resources for the 'hu' locale.<br/>
foodmart.measures.caption=Hungarian Measures<br/>
foodmart.dimension.store.country.caption=Orsz\u00E1g<br/>

foodmart.dimension.store.name.property_manager.caption =\u00C1ruh\u00E1z
vezet\u0151<br/>
foodmart.dimension.store.country.member.caption =store_country_caption_hu<br/>
foodmart.dimension.store.name.property_type.caption =Tipusa<br/>
foodmart.dimension.store.name.caption =Megnevez\u00E9s<br/>
foodmart.dimension.store.state.caption =\u00C1llam/Megye<br/>
foodmart.dimension.store.name.property_type.column =store_type_caption_hu<br/>
foodmart.dimension.store.name.property_storesqft.caption =M\u00E9ret
n.l\u00E1b<br/>
foodmart.dimension.store.allmember.caption =Minden \u00C1ruh\u00E1z<br/>
foodmart.dimension.store.caption =\u00C1ruh\u00E1z<br/>

foodmart.cube.sales.caption =Forgalom<br/>
foodmart.dimension.store.city.caption =V\u00E1ros<br/>
foodmart.cube.sales.measure.unitsales =Eladott db</code>
</blockquote>
<!--
#############################
##  8. Aggregate tables #####
############################# -->
<h3>8. Aggregate tables<a name="Aggregate_tables">&nbsp;</a></h3>

<p>Aggregate tables are a way to improve Mondrian's performance when
the fact table contains a huge number of rows: a million or more. An
aggregate table is essentially a pre-computed summary of the data in
the fact table.</p>

<p>Let's look at a simple aggregate table.</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Cube">Cube</a> name="Sales"&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Table">Table</a> name="sales_fact_1997"&gt;</div>
                <div style="padding-left:60px">&lt;<a href="#XML_AggName">AggName</a> name="agg_c_special_sales_fact_1997"&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_AggFactCount">AggFactCount</a> column="FACT_COUNT"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_AggMeasure">AggMeasure</a> name="[Measures].[Store Cost]" column="STORE_COST_SUM"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_AggMeasure">AggMeasure</a> name="[Measures].[Store Sales]" column="STORE_SALES_SUM"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_AggLevel">AggLevel</a> name="[Product].[Product Family]" column="PRODUCT_FAMILY"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_AggLevel">AggLevel</a> name="[Time].[Quarter]" column="TIME_QUARTER"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_AggLevel">AggLevel</a> name="[Time].[Year]" column="TIME_YEAR"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_AggLevel">AggLevel</a> name="[Time].[Quarter]" column="TIME_QUARTER"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_AggLevel">AggLevel</a> name="[Time].[Month]" column="TIME_MONTH"/&gt;</div>
                <div style="padding-left:60px">&lt;/<a href="#XML_AggName">AggName</a>&gt;</div>
            <div style="padding-left:40px;">&lt;/<a href="#XML_Table">Table</a>&gt;</div>
    <br/>
            <div style="padding-left:40px;">&lt;!-- Rest of the cube definition --&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_Cube">Cube</a>&gt;</div>
    </code>
</blockquote>

<p>The <code>&lt;<a href="#XML_AggForeignKey">AggForeignKey</a>&gt;</code>
element, not shown here, allows you to reference a dimension table
directly, without including its columns in the aggregate table. It is
described in the

<a href="aggregate_tables.html#Another_aggregate_table">aggregate
tables guide</a>.</p>

<p>In practice, a cube which is based upon a very large fact table may
have several aggregate tables. It is inconvenient to declare each
aggregate table explicitly in the schema XML file, and luckily there is
a better way. In the following example, Mondrian locates aggregate
tables by pattern-matching.</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Cube">Cube</a> name="Sales"&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Table">Table</a> name="sales_fact_1997"&gt;</div>
                <div style="padding-left:60px">&lt;<a href="#XML_AggPattern">AggPattern</a> pattern="agg_.*_sales_fact_1997"&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_AggFactCount">AggFactCount</a> column="FACT_COUNT"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_AggMeasure">AggMeasure</a> name="[Measures].[Store Cost]" column="STORE_COST_SUM"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_AggMeasure">AggMeasure</a> name="[Measures].[Store Sales]" column="STORE_SALES_SUM"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_AggLevel">AggLevel</a> name="[Product].[Product Family]" column="PRODUCT_FAMILY"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_AggLevel">AggLevel</a> name="[Time].[Quarter]" column="TIME_QUARTER"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_AggLevel">AggLevel</a> name="[Time].[Year]" column="TIME_YEAR"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_AggLevel">AggLevel</a> name="[Time].[Quarter]" column="TIME_QUARTER"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_AggLevel">AggLevel</a> name="[Time].[Month]" column="TIME_MONTH"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_AggExclude">AggExclude</a> name="agg_c_14_sales_fact_1997"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_AggExclude">AggExclude</a> name="agg_lc_100_sales_fact_1997"/&gt;</div>
                <div style="padding-left:60px">&lt;/<a href="#XML_AggPattern">AggPattern</a>&gt;</div>
            <div style="padding-left:40px;">&lt;/<a href="#XML_Table">Table</a>&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_Cube">Cube</a>&gt;</div>
    </code>
</blockquote>

<p>It tells Mondrian to treat all tables which match the pattern <code>"agg_.*_sales_fact_1997"</code>
as aggregate tables, except <code>"agg_c_14_sales_fact_1997"</code>
and <code>
"agg_lc_100_sales_fact_1997"</code>. Mondrian uses rules to deduce the
roles of the columns in those tables, so it's important to adhere to
strict naming conventions. The naming conventions are described in the
<a href="aggregate_tables.html#Recognizing_aggregates">aggregate tables
guide</a>.</p>
<p>The performance guide has advice on <a
 href="performance.html#Choosing_aggregate_tables">choosing
aggregate tables</a>.</p>
<!--
###########################
##  9. Access-control #####
########################### -->
<h3>9. Access-control<a name="Access_control">&nbsp;</a></h3>

<p>OK, so now you've got all this great data, but you don't everyone to
be able to read all
of it. To solve this, you can define an access-control profile, called
a <dfn>Role</dfn>, as part of the
schema, and set this role when establishing a connection.</p>
<!--
#############################
##  9.1 Defining a role #####
############################# -->
<h1>9.1 Defining a role<a name="Defining_roles">&nbsp;</a></h1>

<p>Roles are defined by <code><a href="#XML_Role">&lt;Role&gt;</a></code>
elements,
which occur as direct children of the <code><a href="#XML_Schema">&lt;Schema&gt;</a></code>
element, after the last <code><a href="#XML_Cube">&lt;Cube&gt;</a></code>.
Here is an example of a role:</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Role">Role</a> name="California manager"&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_SchemaGrant">SchemaGrant</a> access="none"&gt;</div>
                <div style="padding-left:60px">&lt;<a href="#XML_CubeGrant">CubeGrant</a> cube="Sales" access="all"&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_DimensionGrant">DimensionGrant</a> hierarchy="[Measures]" access="all"/&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_HierarchyGrant">HierarchyGrant</a> hierarchy="[Store]" access="custom" topLevel="[Store].[Store Country]"&gt;</div>
                        <div style="padding-left:100px;">&lt;<a href="#XML_MemberGrant">MemberGrant</a> member="[Store].[USA].[CA]" access="all"/&gt;</div>
                        <div style="padding-left:100px;">&lt;<a href="#XML_MemberGrant">MemberGrant</a> member="[Store].[USA].[CA].[Los Angeles]" access="none"/&gt;</div>
                    <div style="padding-left:80px;">&lt;/<a href="#XML_HierarchyGrant">HierarchyGrant</a>&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_HierarchyGrant">HierarchyGrant</a> hierarchy="[Customers]" access="custom" topLevel="[Customers].[State Province]" bottomLevel="[Customers].[City]"&gt;</div>
                        <div style="padding-left:100px">&lt;<a href="#XML_MemberGrant">MemberGrant</a> member="[Customers].[USA].[CA]" access="all"/&gt;</div>
                        <div style="padding-left:100px">&lt;<a href="#XML_MemberGrant">MemberGrant</a> member="[Customers].[USA].[CA].[Los Angeles]" access="none"/&gt;</div>
                    <div style="padding-left:80px;">&lt;/<a href="#XML_HierarchyGrant">HierarchyGrant</a>&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_HierarchyGrant">HierarchyGrant</a> hierarchy="[Gender]" access="none"/&gt;</div>
                <div style="padding-left:60px">&lt;/<a href="#XML_CubeGrant">CubeGrant</a>&gt;</div>
            <div style="padding-left:40px;">&lt;/<a href="#XML_SchemaGrant">SchemaGrant</a>&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_Role">Role</a>&gt;</div>
    </code>
</blockquote>

<p>A <code><a href="#XML_SchemaGrant">&lt;SchemaGrant&gt;</a></code>

defines the default access for
objects in a schema. The <code>access</code> attribute can be "all" or
"none";
this access can be overridden for specific objects. In this case,
because
<code>access="none"</code>, a user would only be able to browse the
"Sales" cube,
because it is explicitly granted.</p>

<p>A <code><a href="#XML_CubeGrant">&lt;CubeGrant&gt;</a></code>
defines the access to
a particular cube. As for <code><a href="#XML_SchemaGrant">&lt;SchemaGrant&gt;</a></code>,
the access attribute can be "all", "custom" or "none", and can
be overridden for specific sub-objects in the cube.</p>

<p>A <code><a href="#XML_DimensionGrant">&lt;DimensionGrant&gt;</a></code>
defines access to a dimension. The access attribute can be "all", "custrom"
or "none". An access level of "all" means that all the child hierarchies
of the dimension will get inherited access. An access level of "custom" means
that the role does not get an inherent access to the child hierarchies,
unless the role is explicitely granted using a
<a href="#XML_HierarchyGrant">&lt;HierarchyGrant&gt;</a> element.</p>

<p>A <code><a href="#XML_HierarchyGrant">&lt;HierarchyGrant&gt;</a></code>

defines
access to a hierarchy. The access attribute can be "all", meaning all
members
are visible; "none", meaning the hierarchy's very existence is hidden
from the
user; and "custom". With custom access, you can use the <code>topLevel</code>
attribute to define the top level which is visible (preventing users
from seeing
too much of the 'big picture', such as viewing revenues rolled up to
the <code>
Store Country</code> level); or use the <code>bottomLevel</code>
attribute to
define the bottom level which is visible (here, preventing users from
invading
looking at individual customers' details); or control which sets of
members the
user can see, by defining nested <code><a href="#XML_MemberGrant">&lt;MemberGrant&gt;</a></code>
elements.</p>

<p>You can only define a <code><a href="#XML_MemberGrant">&lt;MemberGrant&gt;</a></code>

element if its enclosing <code><a href="#XML_HierarchyGrant">&lt;HierarchyGrant&gt;</a></code>
has <code>access="custom"</code>. Member grants give (or remove)
access to a
given member, and all of its children. Here are the rules:</p>

<ol>
  <li><b>Members inherit access from their parents</b>. If you deny
access to California, you won't be able to see San Francisco.</li>
  <li><b>Grants are order-dependent</b>. If you grant access to USA,
then deny access to Oregon, then you won't be able to see Oregon, or
Portland. But if you were to deny access to Oregon, then grant access
to USA, you can effectively see everything.</li>
  <li><b>A member is visible if any of its children are visible</b>.
Suppose you deny access to USA, then grant access to California. You
will be able to see USA, and California, but none of the other states.
The totals against USA will still reflect all states, however.
If the parent HierarchyGrant specifies a top level, only the parents
equal or below this level will be visible. Similarly, if a bottom level
is specified, only the children above or equal to the level are visible.</li>

<li><b>Member grants don't override the hierarchy grant's top- and
bottom-levels</b>. If you set <code>topLevel="[Store].[Store State]"</code>,
and grant access to California, you won't be able to see USA.
Member grants do not override the topLevel and bottomLevel attributes.
You can grant or deny access to a member of any level, but the top
and bottom constraints have precedence on the explicit member grants.</li>
</ol>

<p>In the example, the user will have access to California, and all of
the
cities in California except Los Angeles. They will be able to see USA
(because
its child, California, is visible), but no other nations, and not All
Stores
(because it is above the top level, <code>Store Country</code>).</p>

<!--
###########################
##  9.2 Rollup policy #####
########################### -->
<h1>9.2 Rollup policy<a name="Rollup_policy">&nbsp;</a></h1>

<p>A <dfn>rollup policy</dfn> determines how mondrian computes a member's total 
if the current role cannot see all of that member's children. Under the default 
rollup policy, called 'full', the total for that member includes contributions 
from the children that are not visible. For example, suppose that Fred belongs to a role 
that can see <code>[USA].[CA]</code> and <code>[USA].[OR]</code> but not <code>[USA].[WA]</code>. 
If Fred runs the query</p>

<blockquote><code>SELECT {[Measures].[Unit Sales]} ON COLUMNS,<br/>
&nbsp;&nbsp;&nbsp; {[[Store].[USA], Store].[USA].Children} ON ROWS<br/>
	FROM [Sales]</code></blockquote>

<p>the query returns</p>

<blockquote>
  	<table class="grayTable" id="table8"  >
      	<tr>
			<td align="left"><code>[Customer]</code></td>
			<td><code>[Measures].[Unit Sales]</code></td>
		</tr>
		<tr>
			<td align="left"><code>[USA]</code></td>
			<td align="right">266,773</td>
      	</tr>
      	<tr>
        	<td align="left"><code>[USA].[CA]</code></td>
        	<td align="right">74,748</td>
      	</tr>
      	<tr>
        	<td align="left"><code>[USA].[OR]</code></td>
        	<td align="right">67,659</td>
      	</tr>
    </table>
</blockquote>

<p>Note that <code>[USA].[WA]</code> is not returned, per the access-control
policy, but the total includes the total from Washington (124,366) that
Fred cannot see. For some applications, this is not appropriate.
In particular, if the dimension has a small number of members,
the end-user may be able to deduce the values of the members
which they do not have access to.</p>

<p>To remedy this, a role can apply a different rollup policy to a hierarchy.
The policy describes how a total is calculated for a particular member if
the current role can only see some of that 
member's children:</p>

<ul>
<li><b>Full</b>. The total for that member includes all children. This is the 
default policy if you don't specify the <code>rollupPolicy</code> attribute.</li>
<li><b>Partial</b>. The total for that member includes only accessible children.</li>
<li><b>Hidden</b>. If any of the children are inaccessible, the total is hidden.</li>
</ul>

<p>Under the 'partial' policy, the <code>[USA]</code> total is the sum of the 
accessible children <code>[CA]</code> 
and <code>[OR]</code>:</p>

<blockquote>
  	<table class="grayTable" id="table9"  >
      	<tr>
			<td align="left"><code>[Customer]</code></td>
			<td><code>[Measures].[Unit Sales]</code></td>
		</tr>
		<tr>
			<td align="left"><code>[USA]</code></td>
			<td align="right">142,407</td>
      	</tr>
      	<tr>
        	<td align="left"><code>[USA].[CA]</code></td>
        	<td align="right">74,748</td>
      	</tr>
      	<tr>
        	<td align="left"><code>[USA].[OR]</code></td>
        	<td align="right">67,659</td>
      	</tr>
      	</table>
</blockquote>

<p>Under 'hidden' policy, the [USA] total is hidden because one of its children 
is not accessible:</p>

<blockquote>
  	<table class="grayTable" id="table10">
      	<tr>
			<td align="left"><code>[Customer]</code></td>
			<td><code>[Measures].[Unit Sales]</code></td>
		</tr>
		<tr>
			<td align="left"><code>[USA]</code></td>
			<td align="right">-</td>
      	</tr>
      	<tr>
        	<td align="left"><code>[USA].[CA]</code></td>
        	<td align="right">74,748</td>
      	</tr>
      	<tr>
        	<td align="left"><code>[USA].[OR]</code></td>
        	<td align="right">67,659</td>
      	</tr>
      	</table>
</blockquote>

<p>The policy is specified per role and hierarchy. In the following example, the role sees partial totals for the 
<code>[Store]</code> hierarchy but full totals for <code>[Product]</code>.</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Role">Role</a> name=&quot;South Pacific manager&quot;&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_SchemaGrant">SchemaGrant</a> access=&quot;none&quot;&gt;</div>
                <div style="padding-left:60px;">&lt;<a href="#XML_CubeGrant">CubeGrant</a> cube=&quot;Sales&quot; access=&quot;all&quot;&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_HierarchyGrant">HierarchyGrant</a> hierarchy=&quot;[Store]&quot; access=&quot;custom&quot; <i><b>rollupPolicy=&quot;partial&quot; </b></i>topLevel=&quot;[Store].[Store Country]&quot;&gt;</div>
                        <div style="padding-left:100px;">&lt;<a href="#XML_MemberGrant">MemberGrant</a> member=&quot;[Store].[USA].[CA]&quot; access=&quot;all&quot;/&gt;</div>
                        <div style="padding-left:100px;">&lt;<a href="#XML_MemberGrant">MemberGrant</a> member=&quot;[Store].[USA].[CA].[Los Angeles]&quot; access=&quot;none&quot;/&gt;</div>
                    <div style="padding-left:80px;">&lt;/<a href="#XML_HierarchyGrant">HierarchyGrant</a>&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_HierarchyGrant">HierarchyGrant</a> hierarchy=&quot;[Customers]&quot; access=&quot;custom&quot; <i><b>rollupPolicy=&quot;full&quot;</b></i> topLevel=&quot;[Customers].[State Province]&quot; bottomLevel=&quot;[Customers].[City]&quot;&gt;</div>
                        <div style="padding-left:100px;">&lt;MemberGrant member=&quot;[Customers].[USA].[CA]&quot; access=&quot;all&quot;/&gt;</div>
                        <div style="padding-left:100px;">&lt;MemberGrant member=&quot;[Customers].[USA].[CA].[Los Angeles]&quot; access=&quot;none&quot;/&gt;</div>
                    <div style="padding-left:80px;">&lt;/<a href="#XML_HierarchyGrant">HierarchyGrant</a>&gt;</div>
                    <div style="padding-left:80px;">&lt;<a href="#XML_HierarchyGrant">HierarchyGrant</a> hierarchy=&quot;[Gender]&quot; access=&quot;none&quot;/&gt;</div>
                <div style="padding-left:60px;">&lt;/<a href="#XML_CubeGrant">CubeGrant</a>&gt;</div>
            <div style="padding-left:40px;">&lt;/<a href="#XML_SchemaGrant">SchemaGrant</a>&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_Role">Role</a>&gt;</div>
    </code>
</blockquote>

<p>This example also shows existing features, such as how hierarchy grants can be restricted using 
<code>topLevel</code> and/or <code>bottomLevel</code> attributes, and how a role 
can be prevented from seeing a hierarchy using access=&quot;none&quot;.</p>

<!--
#########################
##  9.3 Union roles #####
######################### -->
<h1>9.3 Union roles<a name="Union_roles">&nbsp;</a></h1>
<p>A union role combines several roles, and has the sum of their privileges.</p>
<p>A union role can see a particular schema object if one or more of its
constituent roles can see it. Similarly, the rollup policy of a union role with
respect to a particular hierarchy is the least restrictive of all of the roles'
rollup policies.</p>

<p>Here is an example showing the syntax of a union role.</p>

<blockquote style="text-indent: -20px">
    <code>
        <div style="padding-left:20px;">&lt;<a href="#XML_Role">Role</a> name=&quot;Coastal manager&quot;&gt;</div>
            <div style="padding-left:40px;">&lt;<a href="#XML_Union">Union</a>&gt;</div>
                <div style="padding-left:60px">&lt;<a href="#XML_RoleUsage">RoleUsage</a> roleName=&quot;California manager&quot;/&gt;</div>
                <div style="padding-left:60px">&lt;<a href="#XML_RoleUsage">RoleUsage</a> roleName=&quot;Eastern sales manager&quot;/&gt;</div>
            <div style="padding-left:40px;">&lt;/<a href="#XML_Union">Union</a>&gt;</div>
        <div style="padding-left:20px;">&lt;/<a href="#XML_Role">Role</a>&gt;</div>
    </code>
</blockquote>

<p>The constituent roles &quot;California manager&quot; and &quot;Eastern sales manager&quot; may be
regular roles, user-defined roles or union roles, but they must be declared
earlier in the schema file. The &quot;Coastal manager&quot; role will be able to see any
member that or a &quot;California manager&quot; and &quot;Eastern sales manager&quot;. It will be
able to see all the cells at the intersection of these members, plus it will be
able to see cells that neither role can see: for example, if only &quot;California
manager&quot; can see <code>[USA].[CA].[Fresno]</code>, and only &quot;Eastern sales
manager&quot; see the <code>[Sales Target]</code> measure, then &quot;Coastal manager&quot;
will be able to see the sales target for Fresno, which neither of the
constituent roles have access to.</p>
<h1>9.4 Setting a connection's role<a name="Setting_a_connections_role">&nbsp;</a></h1>

<p>A role only has effect when it is associated with a connection. By
default,
connections have a role which gives them access to every
cube in that connection's schema.</p>

<p>Most databases associate roles (or 'groups') with users, and
automatically
assign them when users log in. However, Mondrian doesn't have the
notion of
users, so you have to establish the role in a different way. There are
two ways
of doing this:</p>

<ol>
  <li><b>In the connect string</b>. If you specify the <code>Role</code>
keyword in the connect string, the connection will adopt that role. You can
	specify multiple role names separated by commas, and a union role will be
	created; if a role name contains a comma, escape it with an extra comma. See
    <a href="api/mondrian/olap/DriverManager.html"> class DriverManager</a>
for examples of connect string syntax.</li>
  <li><b>Programmatically</b>. Once your application has established a
connection, call the method <a
 href="api/mondrian/olap/Connection.html#setRole%28mondrian.olap.Role%29">
Connection.setRole(Role)</a>. You can create a Role programmatically
(see <a href="api/mondrian/olap/Role.html">interface Role</a> and
the <a href="developer_notes.html">developer's note link</a> for more
details), or look one up using the method <a
 href="api/mondrian/olap/Schema.html#lookupRole%28java.lang.String%29">

Schema.lookupRole(String)</a>.</li>
</ol>
<!--
######################################
##  10. Appendix A: XML elements #####
###################################### -->
<h3>10. Appendix A: XML elements<a name="Appendix_A_XML_elements">&nbsp;</a></h3>

<table id="AutoNumber2"   class="grayTable" width="500">
   	<tr>
      	<td><b>Element</b></td>
      	<td><b>Description</b></td>
    </tr>
    <tr>
      	<td>&nbsp;</td>
      	<td>&nbsp;</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_Schema" href="xml_schema.html#Schema">Schema</a>&gt;</code></td>
      	<td>Collection of Cubes, Virtual cubes, Shared dimensions, and Roles.</td>
    </tr>
    <tr>
      	<td colspan="2"><i><br/>Logical elements</i></td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_Cube" href="xml_schema.html#Cube">Cube</a>&gt;</code></td>
      	<td>A collection of dimensions and measures, all centered on a fact table.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_VirtualCube" href="xml_schema.html#VirtualCube">VirtualCube</a>&gt;</code></td>
      	<td>A cube defined by combining the dimensions and measures of one or more cubes.
A measure originating from another cube can be a <code>&lt;CalculatedMember&gt;</code>.</td>
    </tr>
    <tr>
        <td><code>&lt;<a name="XML_CubeUsages" href="xml_schema.html#CubeUsages">CubeUsages</a>&gt;</code></td>
        <td>Base cubes that are imported into a virtual cube</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_CubeUsage" href="xml_schema.html#CubeUsage">CubeUsage</a>&gt;</code></td>
      	<td>Usage of a base cube by a virtual cube.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_VirtualCubeDimension" href="xml_schema.html#VirtualCubeDimension">VirtualCubeDimension</a>&gt;</code></td>
      	<td>Usage of a dimension by a virtual cube.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_VirtualCubeMeasure" href="xml_schema.html#VirtualCubeMeasure">VirtualCubeMeasure</a>&gt;</code></td>
      	<td>Usage of a measure by a virtual cube.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_Dimension" href="xml_schema.html#Dimension">Dimension</a>&gt;</code></td>
      	<td>Dimension.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_DimensionUsage" href="xml_schema.html#DimensionUsage">DimensionUsage</a>&gt;</code></td>
      	<td>Usage of a shared dimension by a cube.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_Hierarchy" href="xml_schema.html#Hierarchy">Hierarchy</a>&gt;</code></td>
      	<td>Hierarchy.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_Level" href="xml_schema.html#Level">Level</a>&gt;</code></td>
      	<td>Level of a hierarchy.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_KeyExpression" href="xml_schema.html#KeyExpression">KeyExpression</a>&gt;</code></td>
      	<td>SQL expression used as key of the level, in lieu of a column.</td>
    </tr>
	<tr>
      	<td><code>&lt;<a name="XML_NameExpression" href="xml_schema.html#NameExpression">NameExpression</a>&gt;</code></td>
      	<td>SQL expression used to compute the name of a member, in lieu of <code>Level.nameColumn</code>.</td>
    </tr>
	<tr>
      	<td><code>&lt;<a name="XML_CaptionExpression" href="xml_schema.html#CaptionExpression">CaptionExpression</a>&gt;</code></td>
      	<td>SQL expression used to compute the caption of a member, in lieu of<code>Level.captionColumn</code>.</td>
    </tr>
	<tr>
      	<td><code>&lt;<a name="XML_OrdinalExpression" href="xml_schema.html#OrdinalExpression">OrdinalExpression</a>&gt;</code></td>
      	<td>SQL expression used to sort members of a level, in lieu of <code>Level.ordinalColumn</code>.</td>
    </tr>
	<tr>
      	<td><code>&lt;<a name="XML_ParentExpression" href="xml_schema.html#ParentExpression">ParentExpression</a>&gt;</code></td>
      	<td>SQL expression used to compute a measure, in lieu of <code>Level.parentColumn</code>.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_Property"  href="xml_schema.html#Property">Property</a>&gt;</code></td>
      	<td>Member property. The definition is against a hierarchy or level, but the property will be available to all members.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_PropertyExpression" href="xml_schema.html#PropertyExpression">PropertyExpression</a>&gt;</code></td>
      	<td>SQL expression used to compute the value of a property, in lieu of <code>Property.column</code>.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_Measure" href="xml_schema.html#Measure">Measure</a>&gt;</code></td>
      	<td>Measure.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_CalculatedMember" href="xml_schema.html#CalculatedMember">CalculatedMember</a>&gt;</code></td>
      	<td>A member whose value is derived using a formula, defined as part of a cube.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_NamedSet" href="xml_schema.html#NamedSet">NamedSet</a>&gt;</code></td>
      	<td>A set whose value is derived using a formula, defined as part of a cube.</td>
    </tr>
    <tr>
      	<td colspan="2"><i><br/>Physical elements</i></td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_Table" href="xml_schema.html#Table">Table</a>&gt;</code></td>
      	<td>Fact or dimension table.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_View" href="xml_schema.html#View">View</a>&gt;</code></td>
      	<td>Defines a 'table' using a SQL query, which can have different variants for different underlying databases.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_Join" href="xml_schema.html#Join">Join</a>&gt;</code></td>
      	<td>Defines a 'table' by joining a set of queries.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_InlineTable" href="xml_schema.html#InlineTable">InlineTable</a>&gt;</code></td>
      	<td>Defines a table using an inline dataset.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_Closure" href="xml_schema.html#Closure">Closure</a>&gt;</code></td>
      	<td>Maps a parent-child hierarchy onto a closure table.</td>
    </tr>
    <tr>
      	<td colspan="2"><i><br/>Aggregate Tables</i></td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_AggExclude" href="xml_schema.html#AggExclude">AggExclude</a>&gt;</code></td>
      	<td>Exclude a candidate aggregate table by name or pattern matching.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_AggName" href="xml_schema.html#AggName">AggName</a>&gt;</code></td>
      	<td>Declares an aggregate table to be matched by name.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_AggPattern" href="xml_schema.html#AggPattern">AggPattern</a>&gt;</code></td>
      	<td>Declares a set of aggregate tables by regular expression pattern.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_AggFactCount" href="xml_schema.html#AggFactCount">AggFactCount</a>&gt;</code></td>
      	<td>Specifies name of the column in the candidate aggregate table which contains the number of fact table rows.</td>
    </tr>
	<tr>
		<td><code>&lt;<a name="XML_AggMeasureFactCount" href="xml_schema.html#AggMeasureFactCount">AggMeasureFactCount</a>&gt;</code></td>
		<td>The number of non-null fact table rows associated with a specific fact column. Mondrian can use this information when calculating averages from aggregate table values.</td>
	</tr>
    <tr>
      	<td><code>&lt;<a name="XML_AggIgnoreColumn" href="xml_schema.html#AggIgnoreColumn">AggIgnoreColumn</a>&gt;</code></td>
      	<td>Tells Mondrian to ignore a column in an aggregate table.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_AggForeignKey" href="xml_schema.html#AggForeignKey">AggForeignKey</a>&gt;</code></td>
      	<td>Maps foreign key in the fact table to a foreign key column in the candidate aggregate table.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_AggMeasure" href="xml_schema.html#AggMeasure">AggMeasure</a>&gt;</code></td>
      	<td>Maps a measure to a column in the candidate aggregate table.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_AggLevel" href="xml_schema.html#AggLevel">AggLevel</a>&gt;</code></td>
      	<td>Maps a level to a column in the candidate aggregate table.</td>
    </tr>
    <tr>
      	<td colspan="2"><i><br/>Access control</i></td>
    </tr>
    <tr>
      	<td><code>&lt;<a href="xml_schema.html#Role" name="XML_Role">Role</a>&gt;</code></td>
      	<td>An access-control profile.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a href="xml_schema.html#SchemaGrant" name="XML_SchemaGrant">SchemaGrant</a>&gt;</code></td>
      	<td>A set of rights to a schema.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a href="xml_schema.html#CubeGrant" name="XML_CubeGrant">CubeGrant</a>&gt;</code></td>
      	<td>A set of rights to a cube.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a href="xml_schema.html#HierarchyGrant" name="XML_HierarchyGrant">HierarchyGrant</a>&gt;</code></td>
      	<td>A set of rights to a hierarchy and levels within that hierarchy.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a href="xml_schema.html#MemberGrant" name="XML_MemberGrant">MemberGrant</a>&gt;</code></td>
      	<td>A set of rights to a member and its children.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a href="xml_schema.html#Union" name="XML_Union">Union</a>&gt;</code></td>
      	<td>Definition of a set of rights as the union of a set of roles.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a href="xml_schema.html#RoleUsage" name="XML_RoleUsage">RoleUsage</a>&gt;</code></td>
      	<td>A reference to a Role.</td>
    </tr>
    <tr>
      	<td colspan="2"><i><br/>Extensions</i></td>
    </tr>
    <tr>
      	<td><code>&lt;<a href="xml_schema.html#UserDefinedFunction" name="XML_UserDefinedFunction">UserDefinedFunction</a>&gt;</code></td>
      	<td>Declares a user-defined function.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_CellFormatter" href="xml_schema.html#CellFormatter">CellFormatter</a>&gt;</code></td>
      	<td>Cell formatter.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_MemberFormatter" href="xml_schema.html#MemberFormatter">MemberFormatter</a>&gt;</code></td>
      	<td>Member formatter.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_PropertyFormatter" href="xml_schema.html#PropertyFormatter">PropertyFormatter</a>&gt;</code></td>
      	<td>Property formatter.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_Script" href="xml_schema.html#Script">Script</a>&gt;</code></td>
      	<td>Script fragment to implement an SPI such as user-defined
            function, member formatter, or cell formatter.</td>
    </tr>
    <tr>
      	<td colspan="2"><i><br/>Miscellaneous</i></td>
    </tr>
    <tr>
      	<td><code>&lt;<a href="xml_schema.html#Annotations" name="XML_Annotations">Annotations</a>&gt;</code></td>
      	<td>Holder for annotations.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a href="xml_schema.html#Annotation" name="XML_Annotation">Annotation</a>&gt;</code></td>
      	<td>User-defined property attached to a metadata element.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a href="xml_schema.html#Parameter" name="XML_Parameter">Parameter</a>&gt;</code></td>
      	<td>Part of the definition of a Hierarchy; passed to a MemberReader, if present.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_CalculatedMemberProperty" href="xml_schema.html#MemberProperty">CalculatedMemberProperty</a>&gt;</code></td>
      	<td>Property of a calculated member.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_Formula" href="xml_schema.html#Formula">Formula</a>&gt;</code></td>
      	<td>Holds the formula text within a <code>&lt;NamedSet&gt;</code> or <code>&lt;CalculatedMember&gt;</code>.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_ColumnDefs" href="xml_schema.html#ColumnDefs">ColumnDefs</a>&gt;</code></td>
      	<td>Holder for <code>&lt;ColumnDef&gt;</code> elements.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_ColumnDef" href="xml_schema.html#ColumnDef">ColumnDef</a>&gt;</code></td>
      	<td>Definition of a column in an <code>&lt;InlineTable&gt;</code> dataset.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_Rows" href="xml_schema.html#Rows">Rows</a>&gt;</code></td>
      	<td>Holder for <code>&lt;Row&gt;</code> elements.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_Row" href="xml_schema.html#Row">Row</a>&gt;</code></td>
      	<td>Row in an <code>&lt;InlineTable&gt;</code> dataset.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_Value" href="xml_schema.html#Value">Value</a>&gt;</code></td>
      	<td>Value of a column in an <code>&lt;InlineTable&gt;</code> dataset.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_MeasureExpression" href="xml_schema.html#MeasureExpression">MeasureExpression</a>&gt;</code></td>
      	<td>SQL expression used to compute a measure, in lieu of <code>Measure.column</code>.</td>
    </tr>
    <tr>
      	<td><code>&lt;<a name="XML_SQL" href="xml_schema.html#SQL">SQL</a>&gt;</code></td>
      	<td>The SQL expression for a particular database dialect.</td>
    </tr>
</table>
<br />
<br />

<p>Author: Julian Hyde; last modified May 2011.<br/>
    Version: $Id$
    (<a href="http://p4web.eigenbase.org/open/mondrian/doc/schema.html?ac=22">log</a>)<br/>
    Copyright (C) 2001-2005 Julian Hyde.<br/>
    Copyright (C) 2005-2011 Pentaho and others
</p>

<br />

<!-- doc2web end -->

</body>
</html>
